Why do Spreadsheet users insist in using SUMIF Multiple if Excel provides Pivot Tables and Filters? These tools are quicker to apply but not appropriate for sending in reports to users, other people simply don’t like them and prefer to do all with formulas.
Here you will know a way to construct an Excel SUMIF Multiple using neither VBA nor Array Formulas.
Turn two or more conditions into a single one (see graphic 1).SUMIF Allows Just One Criterion, How Do I Do It?
- Create an auxiliary column
- Use logical functions (IF, AND, OR, etc) or logical operators (<, =, >, etc) to evaluate as many conditions as you need
- Join the result of step 2 with any column with CONCATENATE function (do this when necessary and with fields that contain texts)
Nothing is carved on stone, add as many missing-columns as you need. Make each one retrieves TRUE when any condition is met; you can evaluate any criteria:
- Numeric intervals
- Date periods (Use DATEVALUE to convert dates string to serial numbers)
- Text strings (Use Excel Wildcards when needed)
- All above combined (Join conditions with CONCATENATE)
It Is Time to Create Two Auxiliary Columns
See graphic 2.
Imagine you need to sum [Amount] for each [Product Id] depending on a [Contract Date] period, let’s see…
Set formula for Column F this way CONCATENATE(D2,A2<DATEVALUE(“01/05/2009”)); it retrieves TRUE if the [Contract Date] is older than May 01 2009 and joins this result with [Product Id].
Set formula for Column G this way CONCATENATE(D2,AND(A2>=DATEVALUE(“01/05/2009”),
A2<=DATEVALUE(“31/05/2009”))); it returns TRUE if the [Contract Date] is between May 01 2009 and May 31 2009, and joins this result with [Product Id].
You can create SUMIF 2 criteria, SUMIF 3 criteria, SUMIF N criteria as you require, the limit is your imagination.
Here Is the Magic; A SUMIF Multiple Criteria Spreadsheet
This table (graphic 3) remembers me a Pivot Table but it isn’t; you have built a power SUMIF Multiple.
Here is what the Multiple SUMIF does…
- Sum [Amount] for each [Product Id] which [Contract Date] is older than May 01 2009
- Sum [Amount] for each [Product Id] which [Contract Date] is in the period May 01 2009 – May 31 2009
The Magic Revealed. A COUNTIF 3 Categories
The table shown in graphic 3 was done with a classical SUMIF.
What do you do to make the function “multiple”? See graphic 4; the SUMIF “criteria” argument is now a Power Criterion:
- “C-01TRUE” in B15 involves two conditions (“range” argument is column F)
- “C-01TRUE” in B16 involves three conditions (“range” argument is column G)
- “B-07TRUE” in C15 involves two conditions (“range” argument is column F)
- “B-07TRUE” in C16 involves three conditions (“range” argument is column G)
Download an example and explore the formula in detail.
Tip 1: Always cross check, for this example the total must be 628,839.13. You can use Conditional Formatting to not overlook discrepancies.