Help! Formula with not blank column?
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!
Best Answer
-
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 <> "")
Answers
-
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.
Deborah L. Richardson
Research Admin Coordinator
Clinical Trial Office
Tel (603) 308-9642 | Deborah.l.richardson@hitchcock.org
Dartmouth Hitchcock Medical Center
-
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
-
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 <> "")
-
It worked thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!