Sum Product Help Needed

[Guest]

Hi

I am trying to use SUMPRODUCT to sum the values of a list by 2 conditions.
The list has a date column and a Category column and in my summary table i
would like to summarise all entries by date and column

Category is held in Summary!A3 and month is held in Summary!B1.

Can anyone point out where i am going wrong?

TIA

=SUMPRODUCT(Expenditure!$C$2:$C$100=Summary!A3)*MONTH(Expenditure!$A$2:$A$100=MONTH(Summary!$B$1))

 

[JE McGimpsey]

First of all, it sounds like you might do better with a Pivot Table:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

http://cpearson.com/excel/pivots.htm

If you want to use SUMPRODUCT(), assuming your expenses are in
Expenditures!B2:B100, one way:


=SUMPRODUCT(--(Expenditure!$C$2:$C$100=Summary!A3),
--(MONTH(Expenditure!$A$2:$A$100)=MONTH(Summary!$B$1)),
Expenditure!$B$2:$B$100)





For an explanation of the usage of "--" see

http://www.mcgimpsey.com/excel/doubleneg.html