Countifs calculating more than range

Mona Singh

Hi All,

=COUNTIFS([Execution Team]:[Execution Team], "NA ISG Specialty Event - AH Org", [Start Date]:[Start Date], DATE(2020, 8, 1) <= DATE(2020, 10, 31))

I am using this formula to gather number of events we're having internally. I did this per QTR and change dates, but noticed that it is counting ALL events that are less than or = to 10/31. So it's counting July events and earlier too. I changed some dates and was right, as i added numbers both Q2 and Q3 numbers changed.

Can someone tell me if I am missing something? How do i make sure it doesn't go outside of the range of dates between < =?

Genevieve P.

Hi Mona,

Try this:

=COUNTIFS([Execution Team]:[Execution Team], "NA ISG Specialty Event - AH Org", [Start Date]:[Start Date], AND(@cell >=DATE(2020, 8, 1), @cell <= DATE(2020, 10, 31)))

You just needed to indicate that it's greater or equal to that initial first date... that the column is searching between the dates specified. Does that make sense? I used the @cell function to tell the formula to look in each cell of the previously stated column.

Let me know if this works or if you have any questions!

Cheers,

Genevieve

Mona Singh

I will add, that it won't even recognize 2021, and my total number for Countifs is completely wrong. I am not getting an error, so i didn't think anything was wrong until i showed my managers today, and the math didn't add up. Please help

Genevieve P.

Hi Mona,

Try this:

=COUNTIFS([Execution Team]:[Execution Team], "NA ISG Specialty Event - AH Org", [Start Date]:[Start Date], AND(@cell >=DATE(2020, 8, 1), @cell <= DATE(2020, 10, 31)))

You just needed to indicate that it's greater or equal to that initial first date... that the column is searching between the dates specified. Does that make sense? I used the @cell function to tell the formula to look in each cell of the previously stated column.

Let me know if this works or if you have any questions!

Cheers,

Genevieve

Mona Singh

Thank you Genevieve! That worked. :)

Genevieve P.

Wonderful! Happy to help 🙂