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
-
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
-
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.
-
This is great! Thank you for posting your solution.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!