2013-0603

SUMIF and COUNTIF with date

13 Comments[Author: Category: Other ]

Date can be used as a condition in the Excel functions SUMIF and COUNTIF. But there can be some problems. I show you examples.

sumif and date

SUMIF for the exact date

Example No. 1

You have two choices:

1) You can use a formula with the cell address.

=SUMIF(A2:A12,G1,C2:C12)

2) You can write the date right into the formula. The date must be in the quotation marks.

=SUMIF(A2:A12,”5/3/2013″,C2:C12)

SUMIF for data after the exact date

Example No. 2

1) You can use a formula with the cell address. The > character must be in the quotation marks and must be joined with the cell using &.

=SUMIF(A2:A12,”>”&G4,C2:C12)

2) You can write the date right into the formula. The date and > must be in the quotation marks.

=SUMIF(A2:A12,”>5/3/2013″,C2:C12)

If you need to include the start date, change the condition to >=

SUMIF for data before the exact date

Example No. 3

1) You can use a formula with the cell address. The < character must be in the quotation marks and must be joined with the cell using &.

=SUMIF(A2:A12,”<“&G7,C2:C12)

2) You can write the date right into the formula. The date and < must be in the quotation marks.

=SUMIF(A2:A12,”<5/12/2013”,C2:C12)

If you need to include the end date, change the condition to <=

SUMIF for data between two dates

Example No. 4

You have also two choices:

1) You can use a formula with the cell address.

=SUMIF(A2:A12,”<=”&G11,C2:C12)-SUMIF(A2:A12, “<“&G10,C2:C12)

2) You can write the date right into the formula. The date and < must be in the quotation marks.

=SUMIF(A2:A12,”<=5/12/2013″,C2:C12)-SUMIF(A2:A12,”<5/3/2013”,C2:C12)

The result includes the start and end dates.

 

SUMIFS for data between two dates

Example No. 4

If you are using Excel 2007 and higher, you can use SUMIFS function:

=SUMIFS(C2:C12,A2:A12,”>=5/3/2013″,A2:A12,”<=5/12/2013″)

 

 

For SUMIFS, COUNTIF and COUNTIFS apply exactly the same principles.

Tags:
«
»

Add a comment »13 comments to this article

  1. Gustav March 19, 2020 at 6:52 am

    Here is ultimate API source for coronavirus data:

    https://www.linkedin.com/pulse/data-around-corona-key-datasets-apis-starting-point-new-umbach/

    Let me know if you are able to load this data into excel graph.

    Reply

  2. Ily March 5, 2020 at 3:52 pm

    Hi,
    I have actual data from WHO Coronavirus disease. It is like API and it is daily updated. I want to provide data as an shared Excel file. I’m using SUMIF function but there is a problem with date format. Does anybody know how to provide this kind of data? Thanks and stay healthy.

    Reply

  3. DJ September 1, 2017 at 4:00 pm

    Hello,
    I am having an issue with a formula. I have a cell that counts a total based on two criteria (a group and a status bar). It works just fine, but now I would also like this cell to filter by date submitted within a fiscal year. It looks like it should work but when I test the sheet it gives me an error. Any advice? The part that is not working is the last bit.( M4:M9,”>”&C227). C227 is the date criteria and M:4:M9 is the range of dates submitted.

    =SUM(COUNTIFS($I$4:$I$153,D171,$C$4:$C$153,{“PRO BOARD”,”NON-SELECT/DECLINED E/C”,”PRO REC Y-PENDING FINSEL”,”SELECT LTR/OATH RECEIVED”,”ENLISTED/COMMISSIONED”,”SHIPPED TO NTC/COLLEGIATE”},M4:M9,”>”&C227))

    Reply

    • Paul January 8, 2020 at 2:47 pm

      Hi,

      You can try to change data format on your criteria cell (C227). Sometimes it works different when the criteria format is Date,Text or General.

      Reply

  4. raghv December 5, 2016 at 1:47 pm

    Thanks

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress