Excel - Calculate the sum based on multiple criteria

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

Note that

Solved by rizvisa1

Related :

This document entitled « Excel - Calculate the sum based on multiple criteria » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.