Excel - Calculate the sum based on multiple criteria

March 2017


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

Note that

Solved by rizvisa1


Published by aakai1056.
This document, titled "Excel - Calculate the sum based on multiple criteria," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).