October 2016

I have the following:

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 and 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.

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

Solved by rizvisa1

Related :

- Excel - Calculate the sum based on multiple criteria
- Calculating Due date based on multiple criteria
- Excel - A Count if macro based on multiple criteria
- VBA code to apply multiple filters based on multiple criteria
- Excel - Calculation Based On Dynamic Criteria
- Excel - - if then else with multiple criteria