Countifs Formula Referencing Another Sheet

alwayslearning · 03/30/22

I could use some help with a formula that references another sheet.

The sheet I want to reference is titled: Certified Roster Status

The sheet has a number of columns. But, there are two columns for which I want to use the criteria for the count.

I’d like to get a total count from the column called Status when the word Active is in a cell AND when the same rows have the word Eastern in the column called Division.

Attached is a screenshot showing what I’m trying to count.

Screen Shot 2022-03-29 at 8.05.04 PM.png

Can anyone help write this formula for me? Do we have to take into consideration that there is another word in the Status column that contains the word active (Inactive)? Or can the formula be specific for just the word Active? I really struggle with formulas.

Thank you in advance.

Kelly Moore · 03/30/22

Hey @alwayslearning

The COUNTIFS function does exactly what you want it to do. The syntax is COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3, etc). The number of criteria ranges from only one to whatever. You must always insert them as a range-criteria pair. When more than one criteria is used, COUNTIFS knows that we mean all the criteria must occur on the same row.

If we were using the CONTAINS function we would have to worry about active and inactive. In fact, if we wanted both active and inactive we would definitely use CONTAINS as it would find both. However, when you have a specific term to find, we just use that specific term. It simplifies the formula. Because the term is a text string, we enclose the text in quotes. If the term was a number, we would not use quotes, we would enter the number just plain. Quotes around numbers make smartsheet think the number is text and will make it sometimes lose its number characteristics.

You mentioned we're building a cross sheet reference - that is, we are referencing a different sheet. When one does this, don't forget that you have to physically insert the reference link into your sheet through the formula window. You cannot simply copy paste my formula. Also note that your range names will look different than mine - smartsheet will generate generic range numbers. You can change these names to be able to troubleshoot your formula later. (it's a good practice to change the names)

=COUNTIFS({Certified Roster Status Division column}, "Eastern", {Certified Roster Status Status column}, "Active")

This formula says COUNT IF: we can find on the same row - a cell in Division column = "Eastern" AND a cell in Status Column = "Active"

If you have any questions, don't hesitate to ask.

COUNTIFS Function | Smartsheet Learning Center

https://help.smartsheet.com/function/countifs

Counts the number of times all given conditions in their respective ranges are met.

Kelly

Amanda Alv · 03/30/22

Hi,

To add onto all the great information that Kelly provided, I used a "HAS" formula to ensure that all cells with "Active" were counted, even if another word was also in the same cell as opposed to counting cells that only had the word "Active" in them.

Also keep in mind that when you manually insert your Sheet reference, you should select the entire column by clicking the header, to ensure that all rows are captured as new data is entered...assuming you want the full column of data and not a small portion.

Range 1 should be the Division column, and Range 2 should be the Status column in your reference sheet.

=COUNTIFS({Certified Sheet Roster Range 1}, "Eastern", {Certified Sheet Roster Range 2}, HAS(@cell, "Active"))

Kelly Moore · 03/30/22

Hey @alwayslearning

The COUNTIFS function does exactly what you want it to do. The syntax is COUNTIFS(range1, criteria1, range2, criteria2, range3, criteria3, etc). The number of criteria ranges from only one to whatever. You must always insert them as a range-criteria pair. When more than one criteria is used, COUNTIFS knows that we mean all the criteria must occur on the same row.

If we were using the CONTAINS function we would have to worry about active and inactive. In fact, if we wanted both active and inactive we would definitely use CONTAINS as it would find both. However, when you have a specific term to find, we just use that specific term. It simplifies the formula. Because the term is a text string, we enclose the text in quotes. If the term was a number, we would not use quotes, we would enter the number just plain. Quotes around numbers make smartsheet think the number is text and will make it sometimes lose its number characteristics.

You mentioned we're building a cross sheet reference - that is, we are referencing a different sheet. When one does this, don't forget that you have to physically insert the reference link into your sheet through the formula window. You cannot simply copy paste my formula. Also note that your range names will look different than mine - smartsheet will generate generic range numbers. You can change these names to be able to troubleshoot your formula later. (it's a good practice to change the names)

=COUNTIFS({Certified Roster Status Division column}, "Eastern", {Certified Roster Status Status column}, "Active")

This formula says COUNT IF: we can find on the same row - a cell in Division column = "Eastern" AND a cell in Status Column = "Active"

If you have any questions, don't hesitate to ask.

COUNTIFS Function | Smartsheet Learning Center

https://help.smartsheet.com/function/countifs

Counts the number of times all given conditions in their respective ranges are met.

Kelly

Amanda Alv · 03/30/22

Hi,

To add onto all the great information that Kelly provided, I used a "HAS" formula to ensure that all cells with "Active" were counted, even if another word was also in the same cell as opposed to counting cells that only had the word "Active" in them.

Also keep in mind that when you manually insert your Sheet reference, you should select the entire column by clicking the header, to ensure that all rows are captured as new data is entered...assuming you want the full column of data and not a small portion.

Range 1 should be the Division column, and Range 2 should be the Status column in your reference sheet.

=COUNTIFS({Certified Sheet Roster Range 1}, "Eastern", {Certified Sheet Roster Range 2}, HAS(@cell, "Active"))

alwayslearning · 03/30/22

This was a great lesson from @Kelly Moore and @Amanda Alv. My apologies to Kelly -- my question didn't explain my issue well. Kelly's formula did return results for rows that only contained "Active" in the status column. I am trying to accomplish what Amanda suggested, which is to count all cells with "Active", even if another word was also in the same cell. However, I'm getting an #UNPARSEABLE error.

Here is my screenshot of the formula. (I did change the generic range name numbers).

Screen Shot 2022-03-30 at 6.40.48 AM.png

@Amanda Alv - can you see my error? I get lost in all the parentheses, commas, spaces, etc.

Thank you both for this excellent tutorial.

Kelly Moore · 03/30/22

Hey @alwayslearning

You are missing a quote sign after Active.

If your cell is a multiselect dropdown, that is, you can have multiple selections within the same cell then HAS is the function of choice. If you are extracting the word out of a sentence, you need the HAS or Contains.

Kelly

Amanda Alv · 03/30/22

Awesome! Glad it worked out. And yes, those quotes and commas and parenthesis can make your head spin sometimes :)

alwayslearning · 03/30/22

Thank you both!!!

Mark Agee · 11/30/22

Thanks to all -- this thread and information was a big help to me as well!

Formulas and Functions

Formulas and Functions

Countifs Formula Referencing Another Sheet

Best Answers

COUNTIFS Function | Smartsheet Learning Center

Answers

COUNTIFS Function | Smartsheet Learning Center

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA