Returning a value as a number when cross referencing a sheet

Ty Werven

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!

Brian_Richardson

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.

Ty Werven

@Brian_Richardson

Thank you! What am I doing wrong with the formula though?

Brian_Richardson

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