Count formula with cross sheet reference/criteria
I’m sure this is easy and I am missing something fundamental. I have searched other conversations but can’t debug my formula.
I have a detailed project sheet (that is at max capacity…therefore I have created a separate sheet to do some counts so I can create pie charts and dashboards.
I’d like to count the number of sites in a region and am running into the wrong value being returned or an error message. I am referencing another sheet and am counting the number of sites in a region for the outline level 1….which was my way of counting unique values. Can anyone advise me of my mistake?
This formula returns ‘0’ for NA, which is incorrect
COUNTIFS({MetricsRegion_Internal}, "NA", {MetricsOutlineLevelCount_Internal}, ="1")
This formula gives me an incorrect argument error
COUNTIFS({MetricsCountSite_Internal}, {MetricsRegion_Internal}, "NA", {MetricsOutlineLevelCount_Internal}, ="1")
Thanks!
Ilene
Comments
-
Hi Ilene,
Try this.
COUNTIFS({MetricsCountSite_Internal}, {MetricsRegion_Internal}, "NA", {MetricsOutlineLevelCount_Internal}, 1)
Did it work?
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
The first formula is giving you an incorrect count because you have quotes around the "1". Try removing those and see if that helps.
.
The second formula is returning an incorrect argument error because you are missing a criteria section.
You should have
=COUNTIFS(range 1, criteria 1, range 2, criteria 2, range 3, criteria 3)
You have
=COUNTIFS(range1, range 2, criteria 2, range 3, criteria 3)
Try correcting this and you should be squared away.
-
Oops! I missed the second formula!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I almost missed it too. Initially I just wrote it off as a SUMIFS because of the syntax until I realized they were both COUNTIFS.
-
Thanks!
Now my level of shame is less!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!