Returning a value as a number when cross referencing a sheet
So I am trying to pull numbers from a sheet that dumps into a form into my metric sheet. The problem is in one of the questions people can select they are checking in "2" people but when I cross reference using the formula below it pulls a "1" instead of the "2" I want it to be able to pull a 1 or 2. I will include pictures below.
Sheet where form data gets put into:
Will be pulling room # and Number of Visitors in formula below:
Metric Sheet above. Notice how for 508 on top picture it is a 2 but on metric sheet it is a 1.
What do I need to add to each cells function so it is able to count any number that is in original sheet? It will not need to go past 2 since the options are either 0,1, or 2.
Thank You!
Answers
-
It's because you're counting, instead of summing. So it's counting 1 entry for room 508.
Change that COUNTIFS to a SUMIFS. The arguments are slightly different so be sure to look up the COUNTIFS format and follow it.
-
Thank you! What am I doing wrong with the formula though?
-
Look at SUMIFS arguments, they are different from COUNTIFS. Here's the SUMIFS format
SUMIFS(range,criterion_range1,criterion1,criterion_range2,criterion2...)
So you need
=SUMIFS({Number of Visitors}, {ICU Check-in Range 1}, "504", {ICU Check-In Range 3}, <2)
{Number of Visitors} is a cross-sheet reference pointing at the Number of Visitors column on your form data sheet.
Also I'd recommend rename your "Check in Range 1/2" references to be the column names that you're referencing, it just makes it easier to understand your formula later when you're adjusting or troubleshooting it. You can manage references by right clicking and choose "Manage References".
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!