IF/OR/And
Hello!
I am trying to add a formula to do the following:
- count if something is chosen in a drop box column ("Rentleving Toolbox")
AND also count IF something is chosen in the drop box column also marked as "Central" in the "Region" Column. Any help here?! I've been trying various versions of this:
=SUMIFS([Rentleveling Toolbox]:[Rentleveling Toolbox], "Complete", [Rentleveling Toolbox]:[Rentleveling Toolbox], "N/A No Bumps", [Rentleveling Toolbox]:[Rentleveling Toolbox], "N/A-Less than 1 Year", [Rentleveling Toolbox]:[Rentleveling Toolbox], "N/A-Immaterial", [Rentleveling Toolbox]:[Rentleveling Toolbox], "N/A=MTM Lease", Region:Region, "Central")
Comments
-
So it doesn't matter what is in the Rentleveling Toolbox column as long as there is something, and "Central" is selected in the Region column?
You will need a COUNTIFS instead of a SUMIFS as well.
-
Hi Paul,
Correct - the options are the following in the dropdown:
- Complete
- N/A- No Bumps
- N/A- Less than 1 Year
- N/A- Immaterial
-N/A - MTM Lease
It doesn't matter what is marked, it just needs to be marked with one of them AND be identified as "Central" in the Region column.
I tried the countifs and it is coming out #UNPARSEABLE
=COUNTIFS([Rentleveling Toolbox]1:[Rentleveling Toolbox]978, "Complete"), ([Rentleveling Toolbox]1:[Rentleveling Toolbox]978, "N/A No Bumps"), ([Rentleveling Toolbox]1:[Rentleveling Toolbox]978, "N/A-Less than 1 Year"), ([Rentleveling Toolbox]1:[Rentleveling Toolbox]978, "N/A-Immaterial"), ([Rentleveling Toolbox]1:[Rentleveling Toolbox]978, "N/A=MTM Lease"), (Region1:Region978, "Central")
-
=count(collect([Rentleveling Toolbox]:[Rentleveling Toolbox],[Rentleveling Toolbox]:[Rentleveling Toolbox],or(@cell = "Complete",@cell = "N/A- No Bumps",@cell = "N/A- Less than 1 Year",@cell = "N/A- Immaterial",@cell = "N/A MTM Lease"
I prefer to write it as count(collect as it makes more logical sense to me. I can tell you by looking at the end of the equation you have above that you have an issue with parenthesis. I didn't test the equation I wrote above so it might need some minor tweaks
-
Unfortunately there is no "short" way to do it, so here goes nothin'...
=COUNTIFS(Region:Region, "Central", [Rentleveling Toolbox]:[Rentleveling Toolbox], "Complete") + COUNTIFS(Region:Region, "Central", [Rentleveling Toolbox]:[Rentleveling Toolbox], "N/A No Bumps") + COUNTIFS(Region:Region, "Central", [Rentleveling Toolbox]:[Rentleveling Toolbox], "N/A-Less than 1 Year") + COUNTIFS(Region:Region, "Central", [Rentleveling Toolbox]:[Rentleveling Toolbox], "N/A-Immaterial") + COUNTIFS(Region:Region, "Central", [Rentleveling Toolbox]:[Rentleveling Toolbox], "N/A=MTM Lease")
Give this a shot...
You'll need to make sure that the column names are spelled correctly and the "Specific Text" inside the quotes is EXACTLY as it is in the dropdown box.
-
Ah, I like the count - haven't used that before. However, that doesn't capture my "AND" "Central" Region?
Thoughts there?
-
That's it!!!!!! Rockstar. Thank you so much.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives