CountIFS Formula won't work for different text

Andrea Westrich

I have a formula to count the number of contacts based on the source column. That column is shown in an attached photo and the source is selected via a drop down menu in that column. I am focusing on the Zillow and Zillow-Listing direct contracts.  

This is the formula I use to count Zillow contracts: =COUNTIFS({Master Transaction Data Range 2}, "Zillow", {Master Transaction Data Range 3}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)), {Master Transaction Data Range 4}, "Closed")

The data range 2 is the source column, data range 3 is a date column that indicates date closed and data range 4 is a status column.  

With that, this formula correctly counts the contracts as shown in the photo as 3. Then, when I use this formula for Zillow-Listing Direct, it also returns a count of 3 when it should return a count of 1 (reference photo).  

=COUNTIFS({Master Transaction Data Range 2}, "Zillow-Listing Direct", {Master Transaction Data Range 3}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)), {Master Transaction Data Range 4}, "Closed")

What is wrong and why is it doing this?

Source.jpg

Genevieve P.

Hi @Andrea Westrich

I've tested both of your formulas and I'm getting correct numbers for each type of "Zillow". Is it possible that your sheet actually does have three matches for the second formula?

Can you filter by the same criteria in the sheet and see if there are two other matches?

"Zillow-Listing Direct" between January 1st - March 21st, 2021, with the status "Closed"

In your screen capture it looks like there's already a filter applied, could this filter by hiding the other two rows?

Thanks!

Genevieve

Genevieve P.

Hi @Andrea Westrich

I've tested both of your formulas and I'm getting correct numbers for each type of "Zillow". Is it possible that your sheet actually does have three matches for the second formula?

Can you filter by the same criteria in the sheet and see if there are two other matches?

"Zillow-Listing Direct" between January 1st - March 21st, 2021, with the status "Closed"

In your screen capture it looks like there's already a filter applied, could this filter by hiding the other two rows?

Thanks!

Genevieve

Andrea Westrich

@Genevieve P,

Thank you so much for answering this question. This is a great idea to test the data and I did run a filter on the sheet based on my criteria. It did return 1 for Zillow-Direct and 2 for Zillow. There has to be something I am missing. Any other ideas?

Genevieve P.

Hi @Andrea Westrich

Would you be able to post screen captures of each of the three ranges referenced (the pop-up window with the referenced column) and also a screen capture of how the formula shows up in your sheet? (But please block out sensitive data!)

It sounds like your formula is looking to see if the column contains the word "Zillow" instead of the exact match of "Zillow" etc.

I'd be curious to know if adding an = sign before the "criteria" makes a difference?

=COUNTIFS({Master Transaction Data Range 2}, = "Zillow", {Master Transaction Data Range 3}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 3, 31)), {Master Transaction Data Range 4}, "Closed")

Andrea Westrich

@Genevieve P,

The = sign did not work, but I thought it was a great idea. I went back and reentered the formula from scratch and it worked. I have no idea why as I didn't change anything.

Thanks for your help!

Genevieve P.

No problem! Glad it's working now.