Multi-Dimensional Sum

November 8th, 2005 by admin admin

Assuming you have a set of data as shown:

01 ABC XYZ 10
02 ABC XYZ 20
03 DEF XYZ 30
04 PQR ABC 40
05 UVW XYZ 50

You want to add up those numbers that satisfy the conditions Column A contains “ABC” and Column B contains “XYZ”. So what are some of the solutions?

Solution 1
1. Enter a subtotal function in cell C7. The formula should be “=subtotal(9,C1:C5)”. The cell C7 is chosen instead of cell C6 so that when you do the autofilter later on, it will not be included in the autofilter.
2. Do a autofilter and set the criteria for column A as “ABC” and the criteria for column B as “XYZ”.
3 Cell C7 should return the result 30 (10+20)

Solution 2
Enter in cell C6 (or anywhere you prefer) the formula “=sum(if(A1:A5=”ABC”, if(B1:B5=”XYZ”,C1:C5)))”. Instead of the normal enter key, you need to use shift + ctrl + enter. This is because it is an array formula. And you should get the same results as solution 1 (30).

Leave a Reply

You must be logged in to post a comment.