COUNTIFS range formula while referencing another sheet
Hello,
I am unable to figure out how to count the total number of cells that are in a certain status AND range between 31-60 days while referencing another sheet. I have used the formula below to get a total when a cell is in the "Assiged" state AND is less than 30 days and it worked. I've also provided a screenshot of the two columns that I'm working with.
Here is the formula for the less than 30 days and in the Assigned state:
=COUNTIFS({Referenced Sheet Range 1}, "Assigned", {Referenced Sheet Range 2}, "<=30")
Best Answer
-
You were close! The function is COUNTIFS. It needs to COUNTIFS as opposed to COUNTIF when considering more than one set of criterion range and criterion.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Are you sure you're doing this with quotes around the <=30? That shouldn't work.
But to answer your question, there are two ways to add that range of 31-60 days.
First method, just list the range for number of opened days twice, and give it criteria each time, greater than 30, less than or equal to 60.
=COUNTIFS({Referenced Sheet Range 1}, "Assigned", {Referenced Sheet Range 2}, >30, {Referenced Sheet Range 2}, <=60)
Another way is to use AND and @cell:
=COUNTIFS({Referenced Sheet Range 1}, "Assigned", {Referenced Sheet Range 2}, AND(@cell >30, @cell <=60))
Here, the AND statement tells the formula to look at each cell in the range and determine if it is both greater than 30 AND less than or equal to 60.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff!
Thank you so much for your response! I am sure about the quotes around the <=30. I used that same formula for the other state, "In Progress", and it gave me a value. To make sure it was correct I counted them manually and the numbers matched.
However, I tried your formula and I keep getting #INCORRECTARGUMENT. Here is what I entered:
=COUNTIF({Omnitrackers Range 1}, "Assigned", {Omnitrackers Range 3}, >30, {Omnitrackers Range 3}, <=60). The Omnitracker is the name of the referenced sheet that I am using. Should my Range 1 be the Number of Days Open column or the State column?
-
You were close! The function is COUNTIFS. It needs to COUNTIFS as opposed to COUNTIF when considering more than one set of criterion range and criterion.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
IT WORKED! You are amazing! Thank you, Jeff :)
-
I'm always happy to help!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
