COUTM in one column IF "OPEN" and/or "Planning Stage" in another

Options

I understand the concept of COUNTM in a multi-dropdown in a cell... question is how to only count multiple selections in one cell in a row when a cell in another row is "OPEN" and/or "Planning Stage". The formula I've been trying is...

=COUNTM({Type of Equipment - APX All-band}, IF({Mission Status}, "Open", IF({Mission Status}, "Planning Stage")))

The correct answer I should see is 2, instead I get 38 (which is items that are "Closed" and "Open". I only need "Open"

Couple of screen grabs of where I'm getting the data from.

Source data below...

Form for report below... The number "38" represents items that are "Open" and "Closed". I only need "Open".

Thanks!

Mike Beckstrand

Best Answer

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    Try: =IF(OR([Mission Status]@row = "Open", [Mission Status]@row = "Planning Stage"), COUNTM([Type of Equipment - APX All-Band]@row))

  • Beckstrm
    Beckstrm ✭✭✭
    Options

    Thank you.

    This method gave me an error, "Unparsable". I then changed the [...] to {...} because I'm referencing data from another sheet. That also gave the same error.

    =IF(OR({Mission Status}@row = "Open", {Mission Status}@row = "Planning Stage"), COUNTM({Type of Equipment - APX All-Band}@row))

    Next I tried moving the COUNTM section to the front of the formula with the IF(OR at the back. Same error.

    =COUNTM({Type of Equipment - APX All-Band}@row)), IF(OR({Mission Status}@row = "Open", {Mission Status}@row = "Planning Stage"))

    Next, I took out the "@row" command and inserted another IF(OR(, now I get an answer of 38, again.

    =COUNTM({Type of Equipment - APX All-band}, IF(OR({Mission Status}, "Open", IF(OR({Mission Status}, "Planning Stage")))))

    If I take out the second IF(OR(, I get the same "Unparsable" error.

    The answer should be 2 since I have one line with 2 items in the cell and that line is "Open".

    Head scratcher I know.

    Mike

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    You need a COLLECT function.

    =COUNTM(COLLECT({Type of Equipment - APX All-band}, {Mission Status}, OR(@cell = "Open", @cell = "Planning Stage")))

  • Beckstrm
    Beckstrm ✭✭✭
    Options

    PAUL! THANK YOU!

    So many commands, not enough time in the day! I'm glad there is a community to reach out to and get help!

    Thanks!

    Mike

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!