Countif reference range

I would like to know when in January an issue was logged. Both my month and Issues are referencing another sheet. I want a count of January issue looking at columns Issue 1 through column Issue 4 (four column range).

This works - =COUNTIFS({Month}, "January", {Issue #1}, "002_Bypass Audit")

Does not work - =COUNTIFS({Month}, "January", {Issue #1}, "002_Bypass Audit"), {Issue #2}, "002_Bypass Audit"), {Issue #3}, "002_Bypass Audit"), {Issue #}, "002_Bypass Audit")))

Does not work - =COUNTIFS({Month}, "January", {Issue Range 1-4}, "Research Audit Issue")

Best Answer

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓

    @Joey135 That's really odd. There's something wrong with the column as a whole then. Have you tried deleting the column and re-adding it to see if it works (if doing this isn't too much of a hassle>)

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    edited 04/19/24

    Hey @Joey135,

    It looks like the 1st formula you list that isn't working has too many closed off parenthesis in it and it should only have 1 open and 1 close parenthesis in the formula. It should look like this:

    =COUNTIFS({Month}, "January", {Issue #1}, "002_Bypass Audit", {Issue #2}, "002_Bypass Audit", {Issue #3}, "002_Bypass Audit", {Issue #}, "002_Bypass Audit")

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Joey135
    Joey135 ✭✭✭✭
    edited 04/19/24

    @bisaacs Is there a way to make this version work?

    =COUNTIFS({Month}, "January", {Issue #1}, [Primary Column]@row, {Issue #2}, [Primary Column]@row, {Issue #3}, [Primary Column]@row, {Issue #4}, [Primary Column]@row)

    I tried yours and I got invalid ref.

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @Joey135,

    If you're getting an invalid reference that means one of the columns/rows you're trying to use in the calculation doesn't exist. You'll want to make sure that all the data points you're trying to check are valid

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Joey135
    Joey135 ✭✭✭✭

    @bisaacs I see what happened. I tried again and it returns a 0 when I know there are four. Do you see any reason why my below formula shouldn't work?

    =COUNTIFS({Month}, "January", {Issue #1}, [Primary Column]@row, {Issue #2}, [Primary Column]@row, {Issue #3}, [Primary Column]@row, {Issue #4}, [Primary Column]@row)

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @Joey135,

    For COUNTIFS function all criteria have to be met for it to add to the count. All the columns have the same type and all the data is the same?

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Joey135
    Joey135 ✭✭✭✭

    @bisaacs Yes they all contain text that I can put in " " or I have a column that is referencing what I am looking up Error@row. I can get =COUNTIFS({Month}, "January", {Issue #1}, Error@row) to work but if I try to add the other Issue columns (2-4) it doesn't work.

    Also, in my Issue #3 column I have a Error@row with 021_Market IDs that I know has one in it and it will not count it. I keep getting a 0.

    Working sheet:


  • Joey135
    Joey135 ✭✭✭✭

    @bisaacs My Issue 3 column will not add up at all. Here are supporting screen shots. I will not pick up anything in Test Issue 3 column. I re-created this for February, and it did the same thing. All worked 1, 2, and 4 but not 3.

    Working sheet:

    Reference Sheet:


  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓

    @Joey135 That's really odd. There's something wrong with the column as a whole then. Have you tried deleting the column and re-adding it to see if it works (if doing this isn't too much of a hassle>)

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!