Help with Formula: COUNTIFS using date range and single dropdown data

Nathan Grant

I'm trying to figure out a formula to count how many projects occur within a specific date range as well as have one of three different payment status'.

Here's my working date range formula. I'm trying to AND the date range with the three OR'd payment status'.

=COUNTIF({EISD}, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31)))

My new formula is looking like this so far. I haven't been able to locate a manual or training materials or live person for our Enterprise service so you guys are my last shot. The coding is still bizarre to me. I'm sure it's like this to avoid lawsuits.

=COUNTIFS(AND(Payment Status:Payment Status, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service")),EISD:EISD, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31)))

Here's a description of how it needs to behave:

Output: Count of projects that (fall within a specific date range) AND (have a payment status of "Initial Payment" OR "Fully Paid" OR "100% In-Service")

The EISD column is a Date formatted column. The Payment Status column is a Dropdown (Single Select). Both are on the same sheet.

Nathan Grant

It was very close. I tweaked the column references and added one ) at the end and it worked. Thanks for the help!

Here's the final code.

=COUNTIFS({Payment Status}, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service"), {EISD}, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31)))

I don't know if the column references for mine are special because I'm working in a metrics sheet or what but I tested it and it's working.

James Keuning

Can you break apart the two outputs and get them to work in different columns, and then use an AND formula using the two columns as the source, and then if that still works, substitute the formula into the column references?

Because this looks wrong:

AND(Payment Status:Payment Status, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service"))

it's the Payment Status:Payment Status that is sticking out as wrong, because that does not resolve to a string

Nathan Grant

So I've been able to get two individual counts to work. I just need to AND them together.

=COUNTIF({EISD}, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31)))

=COUNTIF({Payment Status}, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service"))

Both of these work correctly by themselves.

Mike Wilday

Try this. =Countifs assumes the AND when you list several different criteria. So the initial AND was superfluous.

=COUNTIFS(Payment Status:Payment Status, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service"),EISD:EISD, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31))

Does this rendition work? It should count Any Payment status of Initial, Fully, or 100%, and between those two dates.

Nathan Grant

It was very close. I tweaked the column references and added one ) at the end and it worked. Thanks for the help!

Here's the final code.

=COUNTIFS({Payment Status}, OR(@cell = "Initial Payment", @cell = "Fully Paid", @cell = "100% In-Service"), {EISD}, AND(@cell >= DATE(2021, 5, 1), @cell <= DATE(2021, 5, 31)))

I don't know if the column references for mine are special because I'm working in a metrics sheet or what but I tested it and it's working.

Mike Wilday

The column names for yours are special because you are referencing external style sheets. Yes, I see my missing parenthesis. Nice catch. I'll update my answer too.