COUNTIF Error

cthomson

I am trying to count the number of submissions that came from the US in the first quarter, but the formula keeps failing. Any ideas?

=COUNTIF(({2024 Range 4}, "United States") + COUNTIF({2024 Range 4}, "US") + COUNTIF({2024 Range 4}, "USA") AND(COUNTIF({2024 Range 1}, "1Q")))

DKazatsky2

Hi @cthomson,

The are most likely getting a parsing error due to the AND section - the syntax is not correct. I would suggest using a COUNTIFS, which already has a built in AND, coupled with an OR. Give the following a try.

=COUNTIFS({2024 Range 4}, OR(@cell = "United States", @cell = "US", @cell = "USA"), {2024 Range 1}, "1Q")

Hope this helps,

Dave

cthomson

https://community.smartsheet.com/discussion/comment/467749#Comment_467749

That one didn't seem to work but, I then tried this: =COUNTIFS({2024 Range 4}, "United States", {2024 Range 1}, "1Q")

which kind of worked. But the problem is some people put "United States" or "US" or "USA" so it is not pulling all of them. Is there a way to do it so that it pulls all of them? With the formula above I got the right number for "united states" first quarter submissions

DKazatsky2

@cthomson,

When you say the formula I provided didn't work, what results are you seeing? In my setup, it is working as expected.

cthomson

https://community.smartsheet.com/discussion/comment/467765#Comment_467765

The sheet I am pulling from is populated by a form so the country is a column vs a row. So I am referencing a whole column to first find "United States" "USA and "US", and then I need it to search a different column to see where it also says "1Q" in the same row as the country I am searching for. The formula I tried the second time works for the countries that were only entered in one way, but not the ones like US that people entered in multiple ways.

DKazatsky2

@cthomson,

Can you provide screen shots of your setup? I am not quite following. Please ensure to hide any sensitive data.

cthomson

https://community.smartsheet.com/discussion/comment/467857#Comment_467857

I just got it to work! This is what I ended up doing:

=COUNTIFS({2024 Range 4}, "United States", {2024 Range 1}, "1Q") + COUNTIFS({2024 Range 4}, "US", {2024 Range 1}, "1Q") + COUNTIFS({2024 Range 4}, "USA", {2024 CSR Range 1}, "1Q")

THANK YOU for all of your help! Appreciate the guidance!