# COUNTIFS range formula while referencing another sheet

Options
edited 06/16/22

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

Tags:

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

Are you sure you're doing this with quotes around the <=30? That shouldn't work.

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

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?

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

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

• ✭✭✭✭✭✭
Options

I'm always happy to help!

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!