COUNTIF Formula referencing two columns
I'm trying to get a count of sites from another sheet for City and State columns and it's not working...can someone tell me what I'm doing wrong?
Because some cities in different state have the same name, I need it to also count if the state matches. Here is my formula:
Reference Sheet - {Master Data Sheet Range City State}
=COUNTIFS({Master Data Sheet Range City State}, [Major Cities (in order by most populous to least)]@row:State@row)
=COUNTIFS({Master Data Sheet Range City State}, [Major Cities (in order by most populous to least)]@row:State@row)
Answers
-
@laura.sandoval you need to separate your criteria,
One of your Cross sheet Ranges will be City, and the second Cross Sheet reference will be State, then your formula would be something like..
=COUNTIFS({Master Data Sheet Range City}, [Major Cities (in order by most populous to least)]@row, {Master Data Sheet Range State}, State@row)
Let me know if that makes sense!
-
Thank you! I fixed it but I think something is still wrong because it's not counting any of the city/states....any thoughts? @Samuel Mueller
=COUNTIFS({Master Data Sheet City Range}, [Major Cities (in order by most populous to least)]@row, {Master Data Sheet State Range}, State@row)
-
@laura.sandoval Just want to confirm that when you created the reference, you clicked on the column header to select the entire column. The image you have above looks like you selected only one cell.
-
You mean when referencing the other sheet? I did :( and I converted it to the column formula to see if it worked and it didn't read any of the city/state matches from the other sheet I am trying to reference. @Samuel Mueller
-
@laura.sandoval try removing the city, and just count the state, see if that gets any counts returned.
=COUNTIFS({Master Data Sheet State Range}, State@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!