Combination of CountIFS and OR
I am trying to accomplish this:
Count If:
If {Current Development - Initiative}, ="Concur HS4"
And, If {Current Development - Hierarchy}, ="EPIC"
And If either one of these columns is not Empty
{Current Development - Q2'23} <> "", {Current Development - Q3'23} <> ""
This is the formula I used, but it shows #Invalid Operation
=COUNTIFS({Current Development - Initiative}, ="Concur HS4", {Current Development - Hierarchy}, ="EPIC", OR({Current Development - Q2'23} <> "", {Current Development - Q3'23} <> ""))
Any advice is much appreciated :-)
SYLVIA KAY | DIRECTOR PROJECT MANAGEMENT
PLATFORM ENGINEERING & TECHNOLOGY TEAM
AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL
M: 404-664- 1025 | SYLVIA.KAY@AMEXGBT.COM
follow us on twitter: @AmexGBT
follow us on instagram: @AmexGBT
Answers
-
@Sylvia Kay The #INVALID OPERATION error points to an operator issue in your formula. In this case, you're missing commas before the "not equal" signs in your OR:
Also, the equal signs in front of your text criteria are not needed. This is because the = is implied by listing the criteria for the range in the criteria range, criteria format required by COUNTIFS.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff, thank you for looking at this!
I made the changes as suggested, but now I get another error: "INVALID DATA TYPE"
This is the current formula:
=COUNTIFS({Current Development - Initiative}, "Concur HS4", {Current Development - Hierarchy}, "EPIC", OR({Current Development - Q2'23}, <>"", {Current Development - Q3'23}, <>""))
SYLVIA KAY | DIRECTOR PROJECT MANAGEMENT
PLATFORM ENGINEERING & TECHNOLOGY TEAM
AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL
M: 404-664- 1025 | SYLVIA.KAY@AMEXGBT.COM
follow us on twitter: @AmexGBT
follow us on instagram: @AmexGBT
-
The cause of that error is: The formula contains or references an incompatible data type, such as =INT("Hello")
In this case, the way we're using OR won't work with COUNTIFS, and that's causing the error.
This is one of those cases where using a helper column will make it work much easier. Try this: Create a helper text/number column in your source sheet called something like "DevelopmentCount" and use this formula:
=IF([Current Development - Q2'23]@row <>"", 1, 0) + IF([Current Development - Q3'23]@row <>"", 1, 0)
Make it a column formula so it applies to all rows. The logic here is that if neither cell has a value in it, the cell will equal 0; if one is not blank, the cell will equal 1, and if both are not blank, the cell will equal 2.
Now on your other sheet:
=COUNTIFS({Current Development - Initiative}, "Concur HS4", {Current Development - Hierarchy}, "EPIC", {Development Count Column Range}, >= 1)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff, thank you so much, this works! Much appreciated!
SYLVIA KAY | DIRECTOR PROJECT MANAGEMENT
PLATFORM ENGINEERING & TECHNOLOGY TEAM
AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL
M: 404-664- 1025 | SYLVIA.KAY@AMEXGBT.COM
follow us on twitter: @AmexGBT
follow us on instagram: @AmexGBT
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!