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

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

Find more posts tagged with

Accepted answers

Paul Newcome

You need a COLLECT function.

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

All comments

Nic Larsen

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

Beckstrm

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

You need a COLLECT function.

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

Beckstrm

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

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.