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).   I would use a formula like the following.


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

Notice how each sumif 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 dataset 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.


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.