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

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

    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.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Ty Werven
    Ty Werven ✭✭✭

    @Brian_Richardson

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

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

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

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!