|

# » Using multiple criteria in SUMIF Function

CATEGORY - Summing VERSION - All Microsoft Excel Versions
Problem:

Summing the total from cells in one column that meet criteria based on a range of dates in another column.

Solution 1:

Use of SUMIF Function (with either DATE or MONTH) or SUMPRODUCT Function (with manually inserted date criteria or cell references to desired dates).
By using the SUMPRODUCT Function, you can either include the desired dates to test for in the
formula:
=SUMPRODUCT((A1:A100>=DATE(2004,9,1)),(A1:A100<=DATE(2004,9,30)),B1:B100)
Or you can have helper cells where you can enter the desired start and end dates to use:
=SUMPRODUCT((A1:A100>=C1),(A1:A100<=D1),B1:B100)
Where cell C1 contains start date, and D1 contains end date.

Solution 2:

Another option is to using DATE Function along with SUMIF Function to calculate all items up to the desired end date and then subtract all items prior to the desired start date, with the result being the total of items within desired range:
=SUMIF(B4:B10,"<"&DATE(2004,10,1),C4:C10)-SUMIF(B4:B10,"<"&DATE(2004,9,1),C4:C10)

Solution 3:

Use a helper column inserted next to the date column to calculate the MONTH value of the list of dates, this value is then used in a SUMIF formula:
=SUMIF(A4:A10,A1,C4:C10)
Screenshot // Using multiple criteria in SUMIF Function

Rate This Tip
 1 2 3 4 5
Rating: 2.71     Views: 214011