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.
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.
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.
The WHO provides 2 different API points:
GHO OData API – https://www.who.int/data/gho/info/gho-odata-api
GHO Athena API – https://www.who.int/data/gho/info/athena-api
But I’m not able to filter Coronavirus data only.
There is API data source from US Centers for Disease Control and Prevention
https://open.cdc.gov/apis.html
I don’t know if they have world data or USA data only.
The most actual numbers about Coronavirus are here
https://ncov2019.live/data
It is only about Coronavirus cases and deaths and it is updated every minute.
Hi,
Is there another live data source? The live web is not working. I need actual Coronavirus data from all countries.
Thanks
Try this page: https://experience.arcgis.com/experience/685d0ace521648f8a5beeeee1b9125cd
They have the most recent data on Coronavirus situation. Very good source is also official World Health Organisation daily reports:
https://www.who.int/emergencies/diseases/novel-coronavirus-2019/situation-reports
Hi,
the latest statistics from all countries are on the worldometers.info
https://www.worldometers.info/coronavirus/
They have live coronavirus (COVID-19) statistics.
Hi,
you can use some of web sources. For example: https://www.worldometers.info/coronavirus/
And import data directly in Excel sheet. In the Excel menu Get Data >> From other sources >> From web
You can also set up automatically update there.
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.
Thanks