Help! Formula with not blank column?

laura.sandoval

Hi SmartSheets community, I need help finishing a formula to count number of sites in a specific city/state. This is what I have so far:

=COUNTIFS({Master Data Sheet City Range}, City@row, {Master Data Sheet State Range}, State@row)

I want to make one other column next to column that has this formula that counts only if the site has an assigned number to it (to identify the site). Hope that makes sense. Help please!

Paul Newcome

There are a few way, but my personal preference is:

=COUNTIFS({Master Data Sheet City Range}, City@row, {Master Data Sheet State Range}, State@row, {Range}, @cell <> "")

DebRichardson

Hi Laura

When using countifs you need to give a range of what you are looking for within multiple columns. COUNTIFS( range1, criterion1, [range2, ​criterion2, ​...] )

The example you shared is incomplete you need to be specific in the formula.

try this =COUNTIFS([Master Data Sheet City Range]:[Master Data Sheet City Range], ="Specific City", [Master Data Sheet State Range]:[Master Data Sheet State Range], ="Specific State")

The formula needs to specify a city and state that you are looking for.

laura.sandoval

So the formula I posted is for the column "Site Count" and it counts any site that has that city and state, it works well! I'd like to add a column next to it that counts sites that have that same city/state BUT is also a numbered site...and that is coming from another sheet that has a master list of all the sites along with their info.

so basically how do I add that into the formula? Like for it to be counted if the numbered sites if it's not blank? @DebRichardson

Paul Newcome

There are a few way, but my personal preference is:

=COUNTIFS({Master Data Sheet City Range}, City@row, {Master Data Sheet State Range}, State@row, {Range}, @cell <> "")

laura.sandoval

It worked thank you!

Paul Newcome

Happy to help. 👍️