Using a formula to Reference another sheet with two variables

I'm trying to create a formula for a report that references another sheet but it needs to reference and check two columns. My crude formula below to illustrate:

=COUNTIF ((RefSheet1Range1)="Windows Server" AND (RefSheet1Range2){Status}="Completed")

This would return a count of the number of Windows Servers that have been completed

Range 1 would ref {Operating System}

Range 2 would ref {Status}


I haven't been able to find a reference in help or community.

Thanks in advance

Best Answer

  • Geoffrey Kemp
    Answer ✓

    Just have to say thanks to the quick response from Smartsheet, here's the solution:

    Since you have multiple criteria I suggest using COUNTIFS function (see: https://help.smartsheet.com/function/countifs). COUNTIFS will allow you to count the number of cells within a range that meet all of the specified criteria.

     I have created a sample and here are some guidelines on how we can achieve the desired result.

     ·        In my main sheet, I listed 3 different OS names and put this formula =COUNTIFS({Reference sheet Range 1}, [OS List]1, {Reference sheet Range 2}, "Completed")

    ·        

    ·        I referenced the range in a different sheet by using cross-sheet reference (see: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets)

    ·        Here's my Sheet which I referenced my range

    Here's the Result of my COUNTIFS: It returns the count of OS in "Completed" status as per OS types.


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!