CountIf with Date range

Julius Benn Sabeniano

Hi Paul,

Yes, CD1 is created date and it was from the source sheet and the Select Week is my selector of the Week (in yellow). I made one cell with a drop-down so that they can select which week to display. See the screenshot below. I just want to align the cut-off per week to Saturday-Friday since the default of WEEKENUMBER function is Monday to Sunday.

Tom Fletcher

Hi @Paul Newcome ,

I was wondering whether you could help me with the following - struggling to get this one to work. The first part of the formula works fine, however when I add in the "AND" date range criteria, it fails:

=COUNTIFS(Location:Location, $Metrics$1, [Incident Type]:[Incident Type], $Metrics@row, AND([Reported Date]:[Reported Date] >= DATE(2020, 1, 1), [Reported Date]:[Reported Date] <= DATE(2020, 1, 31)))

I'm basically trying to sum up the number of 'incident type' for a given location for a particular month.

Any guidance you can provide would be hugely appreciated!

Thank you.

Tom

Paul Newcome

@Tom Fletcher It is the syntax. With COUNTIFS it should be range, criteria, range, criteria. The AND statement is a logical statement which acts as the criteria portion. What you would need is

range, AND(@cell > this, @cell < that)

=COUNTIFS(Location:Location, $Metrics$1, [Incident Type]:[Incident Type], $Metrics@row, [Reported Date]:[Reported Date], AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 1, 31)))

In this particular instance though it looks like you are counting for a full calendar year in which case you could use

=COUNTIFS(Location:Location, $Metrics$1, [Incident Type]:[Incident Type], $Metrics@row, [Reported Date]:[Reported Date], YEAR(@cell) = 2020)

and to avoid some of the more likely issues...

=COUNTIFS(Location:Location, $Metrics$1, [Incident Type]:[Incident Type], $Metrics@row, [Reported Date]:[Reported Date], IFERROR(YEAR(@cell), 0) = 2020)

Ess

@Paul Newcome

Hi Paul, I am trying to get a formula to calculate "Not Started", "In Progress" and "Complete" tasks for specific dates.

Lets say the project runs from July 1st 2020 to June 30th 2023.

I need my tasks rollup pie chart to show me tasks from July 1st 2020 to January 31st 2021 and not future complete roll up of start of project to end of project.

Please help!

Paul Newcome

@Ess It would be along the same lines of the solutions provided above.

=COUNTIFS(Status:Status, "Not Started", Dates:Dates, AND(@cell >= DATE(yyyy, mm, dd), @cell <= DATE(yyyy, mm, dd)))

Ess

@Paul Newcome

I'm getting an error UNPARSEABLE

Paul Newcome

You would need to leave "@cell" as is.

I also didn't realize you were referencing two separate date columns. In that case you are going to want...

=COUNTIFS(Status:Status, "Not Started", [Start Date]:[Start Date], @cell>= DATE(2020,07,01), [End Date]:[End Date], @cell<= DATE(2020,12,31))

Ess

@Paul Newcome Thanks so much! It worked.

Paul Newcome

Happy to help. 👍️

Benjamin Blask

Hello together, i found this post by searching for a Soloution for my Problem.

Im trying to make a Formular that count Rows from another Sheet if they meet 2 criterias the first is to check if the Value from the same sheet where the formular stands is same and the second criteria is to check if the Date is in Range. this is my formular

=COUNTIFS({Meldung Supportbedarf 2020/2021 Bereich 1}; [IT Assurance Leads]@row; AND({Lead Liste Bereich 1}@cell <= DATE(2021;01;01);@cell DATE (2021;12;31))

but i get a unparable error. Someone got an idea?

Amy Shank

I am having a similar issue and could really use some help. I need to count all of the contracts won & lost by month. Similarly I need to sum all of the contracted proposals budgets won/lost by month.

Paul Newcome

@Amy Shank To get the count, it would look something like this...

=COUNTIFS({Won Column}, 1, {Date Won Column}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2021))

Just change the 3 and the 2021 to the appropriate month and year you are wanting to count.

To get the sum, it would look very similar.

=SUMIFS({Budget Column}, {Won Column}, 1, {Date Won Column}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2021))

DeAnna Masterson

--

Tabitha W.

Hi all, I am trying to follow this thread but having some problems. I am referencing this formula on a rollup sheet. Here is my formula and it is returning #UNPARSEABLE:

=COUNTIFS({On the Job Injury Log Range 5}, "Employee Returned To Work", AND{On the Job Injury Log Range 1}, IFERROR(YEAR({On the Job Injury Log Range 1}, 0) = 2020)))

Any help would be appreciated- thanks!

Paul Newcome

@Tabitha W. Try this...

=COUNTIFS({On the Job Injury Log Range 5}, "Employee Returned To Work", {On the Job Injury Log Range 1}, IFERROR(YEAR(@cell, 0) = 2020))