Multiple criteria sums

Solved/Closed
andy - Jul 22, 2010 at 08:42 AM
 andy - Jul 23, 2010 at 07:16 AM
Hello,

I was hoping somebody could help me do this?
A B C
INC-001 05/2009 10
INC-002 07/2010 10
CTN-000 05/2009 10
INC-004 05/2009 10
INC-005 05/2009 10

In the spreadsheet above I want to put in ONE cell (e.g. D1). the sum of all the numbers in column C, where column A contains the letters INC, and column B contains 2009 (answer = 30). I know this can be done by adding another column and the IF function,and then using SUMIF but I would prefer not to do that if possible.

thanks in advance

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jul 22, 2010 at 05:35 PM
Try this

=SUMPRODUCT( (LEFT(A$1:A$5,3)="INC") *( YEAR(B$1:B$5)= 2009)*(C$1:C$5))

You can also use
=SUMPRODUCT( (ISNUMBER(SEARCH("*INC*",A$1:A$20))) *( YEAR(B$1:B$20)= 2009)*(C$1:C$20))
0
FANTASTIC! thanks a lot
0