COUNTIF with date range and multiple criteria

1
votes

I am trying to simplify a process of counting how many of a specified criteria are in a table.

What I need is to count the number of items that meet all of the following criteria:

[BusinessArea] = "Corporate"
[Application] = "CS"
[Status] = "Resolved"
[ResolvedDate] = *if the resolved date is between DateA and DateB eon a 
separate worksheet.

I can do it using VBA or Formulas but I just cannot figure out the date part. I have them figured out separately as:

=IF(AND(Sheet1!I71 >= (TODAY()-7), Sheet1!I71 <TODAY()), TRUE, FALSE)
Where i71 is the [ResolvedDate] (it is searching just this one entry without the other filters.

=COUNTIFS(Table8[Business Area], "Corporate", Table8[Application], "CS")
Where it counts the number of entries that are Corporate_CS entries.

What I currently have:

'=COUNTIFS(Table8[Reported Date],AND(Table8[Reported Date]<='Ticket Summary'!F61, Table8[Reported Date]< TODAY()),Table8[Business Area], "Corporate", Table8[Application], "CS")'

Where F61 is a previous date (beginning of range)

Of which it is returning 9 instead of 6. There are 9 entries that match the criteria, 6 matching the date range and criteria

2
excelvba
Your date formula worked for me. Take a look at Evaluate Formula: [stackoverflow.com/questions/48171896/… - dev1998
@dev1998 The date portion works. And the countif works. I need them together for one single result. Counting the number of entries in table X that are corporate business area with the CS application for the past week (or between two specified dates) Basically when I enter it into excel as this : '=IF(AND(Table8[Reported Date]>='Ticket Summary'!F61, Table8[Reported Date] < TODAY()), COUNTIFS(Table8[Business Area], "Corporate", Table8[Application], "CS"),0)' It is returning 9 instead of 6. There are 9 entries meeting the criteria but only 6 within the specified date - RyanS
If you have code that doesn't work as intended then edit your question to embed it in the post, including any error you might be getting. See minimal reproducible example. - Mathieu Guindon
I added a corrected answer. - dev1998

2 Answers

0
votes

Try this:

=COUNTIFS(Table8[BusinessArea], "Corporate", Table8[Application], "CS",Table8[Status], "Resolved", Table8[ResolvedDate], ">=" & I61, Table8[ResolvedDate], "< " & I71)

I'm assuming that I61 is the start date and I71 is the end date.

I used this as a guide for the dates: count-cells-between-dates

0
votes

Multi-criteria counting is a little tough in Excel. I used to make extra columns and concatenate values. Then you can use countif without too much trouble. But it was messy. You could also use DCOUNTA by setting up your query parameters on another part of your sheet (but it doesn't like tables - only cell ranges). Again, its a little messy, but it's very flexible.

My preferred method is to use the SUMPRODUCT function. Everything can be done in one formula and it works with tables. To get your count using SUMPRODUCT:

=SUMPRODUCT((Table8[BusinessArea]="Corporate")*(Table8[Application]="CS")*(Table8[Status]="Resolved")*(Table8[ResolvedDate]>DateA)*(Table8[ResolvedDate]<DateB))