Sum Multiple Items in 1 Column

The following article will show you how to sum 3 criteria in a single Excel formula. Traditionally when summing multiple items in the same column based on a condition I would add SUMIFS together. For example, if you have data in Column B and you want to sum three items, in this Column (home, work and car).   We might use a formula like the following:

=SUMIFS($C$11:$C$28,$B$11:$B$28,E11)+SUMIFS($C$11:$C$28,$B$11:$B$28,E12)+SUMIFS($C$11:$C$28,$B$11:$B$28,E13)

Where column B is the Description, Column E has the criteria and column C has the amount to sum.

Notice how each SUMIFS is added together to test a condition and add it to the next condition.  The formula while simple becomes large quite quickly.

The above is an example of the data set in the attached file.  You can see I want to sum the column on the left based on the criteria in blue under the Criteria heading.  Well after some investigation and trial and error the following will do the same thing as the above long winded formula.

=SUMPRODUCT(SUMIFS(C11:C28,B11:B28,E11:E13))

It uses SUMPRODUCT and SUMIF together to address all three criteria in the SUMIF formula which sits inside the SUMPRODUCT formula.  

In the attached file I have included both the short and longer formulas.

Another Method for Summing

Other ways to SUM using only SUMPRODUCT include using all of the criteria and repeating that criteria with the addition symbol +.

=SUMPRODUCT(((B11:B28=E11)+(B11:B28=$E$12)+(B11:B28=E13))*C11:C28)

It is always useful to know different methods for consolidating data inside formula.