SUMPRODUCT to calculate amount based on different criteria

Dear all,

I have a spreadsheet with 4 columns

A - Date (DD-MMM)
B - Amount
C - Name
D - Status - there is status of 04 or 05 or 11 or 12 or 1 number from (either 04 or 05 or 11 or 12) along with 01 or 02 or 03. or 09 or 10 e.g. "01,04" or "01,11"

Now i am using the the following "Sumproduct" formula to calculate the amount of the month according to the status e.g. count for the amount of Apr with all status code "11" and the "11" along with other numbers say 04,11 or 05,11 or 10,11. Any way to shorten the formula. Thanks

=SUMPRODUCT(--(X$10:X$497="01,11"),--(MONTH(F$10:F$497)=2),N$10:N$497)+SUMPRODUCT(--(X$10:X$497="02,11"),--(MONTH(F$10:F$497)=2),N$10:N$497)+SUMPRODUCT(--(X$10:X$497="03,11"),--(MONTH(F$10:F$497)=2),N$10:N$497)+SUMPRODUCT(--(X$10:X$497="06,11"),--(MONTH(F$10:F$497)=2),N$10:N$497)+SUMPRODUCT(--(X$10:X$497="04,11"),--(MONTH(F$10:F$497)=2),N$10:N$497)+SUMPRODUCT(--(X$10:X$497="09,11"),--(MONTH(F$10:F$497)=2),N$10:N$497)+SUMPRODUCT(--(X$10:X$497="10,11"),--(MONTH(F$10:F$497)=2),N$10:N$497)+SUMPRODUCT(--(X$10:X$497="11"),--(MONTH(F$10:F$497)=2),N$10:N$497)

Thanks & regards

PJLAu

can you upload example workbook?

If I understand your question correctly and you're summing the contents of column N where dates in column F fall in month 2 and the contents of column X end in "11", then maybe this should work...?

=SUMPRODUCT(--(RIGHT(X$10:X$497,2)="11"),--(MONTH(F$10:F$497)=2),N$10:N$497)

Thx Spencer, this is exactly what I want. You are so awesome.

Thx