Sunday, August 19, 2012

Google Spreadsheet calculate SUM values according to a criteria

Let's see a example of it:

I'm trying to have my spreadsheet that calculates SUM values according to the person's name selected in a drop down list.  I have names in a drop down list in column A. With days totals in column B and Month totals in column C. when an person makes a entry, they select their name and add the day and month amounts. 

I need to have each person have their days totals SUM up in one cell and month totals Sum up in another cell.

So in the above table we want to get the values in the range F3:G6

So by putting following formulas at the mentioned cells in the spreadsheet we will get the results:

F3 =sumproduct(filter(B2:B,A2:A=E3))
F4 =sumproduct(filter(B3:B,A3:A=E4))
F5 =sumproduct(filter(B4:B,A4:A=E5))
G3 =sumproduct(filter(C2:C,A2:A=E3))
G4 =sumproduct(filter(C3:C,A3:A=E4))
G5 =sumproduct(filter(C3:C,A3:A=E5))

By inserting these formulas we will get the results as shown below in the screenshot:

I hope this makes you understand, the functioning of "sumproduct" and "filter" funtions.

No comments:

Post a Comment