IF/OR/And

Options
Nicole Hodges
Nicole Hodges ✭✭✭✭✭✭
edited 12/09/19 in Smartsheet Basics

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 07/20/18
    Options

    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
    Nicole Hodges ✭✭✭✭✭✭
    Options

    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
    L_123 ✭✭✭✭✭✭
    Options

    =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
    Paul Newcome ✭✭✭✭✭✭
    edited 07/20/18
    Options

    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
    Nicole Hodges ✭✭✭✭✭✭
    Options

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

     

    Thoughts there?

  • Nicole Hodges
    Nicole Hodges ✭✭✭✭✭✭
    Options

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

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

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