COUNTIFS range formula while referencing another sheet

Ashley B.

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

Jeff Reisman

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.

Jeff Reisman

@Ashley B.

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.

Ashley B.

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?

Jeff Reisman

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.

Ashley B.

IT WORKED! You are amazing! Thank you, Jeff :)

Jeff Reisman

https://community.smartsheet.com/discussion/comment/333117#Comment_333117

I'm always happy to help!