COUNTIF Formula referencing two columns

laura.sandoval

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)

Samuel Mueller

@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!

laura.sandoval

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)

Samuel Mueller

@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.

laura.sandoval

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

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)