Using a formula to Reference another sheet with two variables

Geoffrey Kemp · 08/05/20

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

Geoffrey Kemp · 08/06/20

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.

Geoffrey Kemp · 08/06/20

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

·