# SUMIF and COUNTIF with date

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 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.

### Add a comment »13 comments to this article

1. 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.

2. 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.

• Where is the API? Do you have a link? I found only PDF with situation report including total and new cases but it is not machine readable document. I think there should be some API on World Health Organization server with Coronavirus numbers data.

3. 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))

• 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.

4. Thanks

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