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: 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:

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!