Using multiple criteria in SUMIF Function

by  About
       

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



2 thoughts on “Using multiple criteria in SUMIF Function

  1. Do you want to read about the syntax and usage of an Excel 2013 or VBA function in your native language and practice with a demo workbook ?

    The facts:
    There are over 600 Excel & VBA functions in Office 2013.
    Excel functions have been translated in 16 languages.
    Microsoft offers over 20,000+ function help webpages in 50+ languages.

    How to navigate fast among so many help pages ?

    This free Ribbon Add-in will help you navigate to Microsoft’s online help pages with embedded workbooks, which can be downloaded to your computer for function inspection and in-depth practice.

Leave a Reply

Your email address will not be published. Required fields are marked *


5 − = three

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>