Google Exceltip.com
Account Icon
Shopping Cart
CheckOut

» Using multiple criteria in SUMIF Function

CATEGORY: Summing
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

Using multiple criteria in SUMIF Function
Rate this tip
12 34 5
  RATING: 3.11
  VIEWS: 91327
  No comments have been submitted.


REGISTERED USERS click here to post comments


GUESTSclick here to Register
Name
Comment Title
Comments


Terms and Conditions of use
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Copyright © 2003 ExcelTip.com
Microsoft, Microsoft Excel is a U.S. registered trademark of Microsoft Corporation