Countifs Formula Referencing Another Sheet

Options

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.

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.

Tags:

Best Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

    Kelly

  • Amanda Alv
    Amanda Alv ✭✭✭✭✭
    Answer ✓
    Options

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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

    Kelly

  • Amanda Alv
    Amanda Alv ✭✭✭✭✭
    Answer ✓
    Options

    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
    alwayslearning ✭✭✭✭
    Options

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

    @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
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Amanda Alv ✭✭✭✭✭
    Options

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

  • alwayslearning
    alwayslearning ✭✭✭✭
    Options

    Thank you both!!!

  • Mark Agee
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!