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
-
@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
-
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!
-
@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.
-
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!
-
@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)
-
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!
-
@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:
-
@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:
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!