Excel - Calculate the sum based on multiple criteria


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. Latest update on September 21, 2012 at 06:37 AM by deri58.
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 (https://ccm.net/).
Excel - Calculate the YTD Cost
Excel/VBA - A macro to change the color of a row