IF/OR/And

Nicole Hodges

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

Paul Newcome

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.

Nicole Hodges

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

L_123

=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

Paul Newcome

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.

Nicole Hodges

Ah, I like the count - haven't used that before. However, that doesn't capture my "AND" "Central" Region?

 

Thoughts there?

Nicole Hodges

That's it!!!!!! Rockstar. Thank you so much.

L_123

=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"),Region:Region,"Central"