Sum Product Help Needed

[Edgar Thoemmes]

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



In article <[email protected]>,
"Edgar Thoemmes" <[email protected]> wrote:

> 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))