Multiple criteria sums

[Solved/Closed]
Report
-
 andy -
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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))
FANTASTIC! thanks a lot