When I ask professionals in my workshops – “Which is the ONE formula you’ll pick if you were asked to choose JUST ONE?” Most of the answers hover around INDEX, VLOOKUP and IF. But they tend to forget Excel Sumif.
You know what MY answer is – Excel SUMIFS()! And I will tell you why.
Excel Sumif Formula
SUMIFS() is a VERY powerful function in Excel. It can accommodate 127 criteria fields!
Let’s see an example – All “M” or males with age of at least 21 are chosen to yield an answer of $28,000. Here 2 criteria fields have been applied.
- Building Dashboards
- Complex calculation involving From-To date ranges
- Functions using similar logic: COUNTIFS(), AVERAGEIFS()
- Differential cumulative/running total
- Condition based analysis
Our latest course, Become an Excel Ninja and a Trainer Consultant will include the following:
- SUMIFS() – Conditional Summation (1 criteria)
- SUMIFS() – Conditional Summation (2 criteria)
- SUMIFS() – Conditional Summation (3 criteria) w. date range
- SUMIFS() – Condition based Selective Cumulative Running Total
- COUNTIFS() – Single/Multiple Criteria: Duplicate Count, Instance No.
Also, read how to use SUMIF Function in Excel