Formula Assistance (COUNTM??)

Alison

Hello all,

My team and I are hoping to build a formula to do the following:

First, identify all items that belong to "Group A," then count how many items the rows that belong to group A have in "Color."

So, for example, the answer for this would be 4 (because there are 4 items in the "Color" column that belong to Group A).

We know that to count the "Color" column, we need to use COUNTM() and we have that working. However, we are not sure how to narrow that down to only the items in a specific group.

Could anyone help us with this?

Alison

Paul Newcome

Try a COUNTM/COLLECT. The COLLECT function will essentially populate the COUNTM function with only those rows from the multi-select based on the criteria you specify.

=COUNTM(COLLECT(Color:Color, Group:Group, "A")

Paul Newcome

I'm not sure I follow. Would there be multiple rows for Group A that you want to count all colors for?

Alison

Correct, it will have a number of rows, filling over 200 rows with Groups A-C, etc.. This is dummy data to be able to post in a public forum.

Paul Newcome

Try a COUNTM/COLLECT. The COLLECT function will essentially populate the COUNTM function with only those rows from the multi-select based on the criteria you specify.

=COUNTM(COLLECT(Color:Color, Group:Group, "A")

Alison

Thanks, Paul, this works great!!

Paul Newcome

Excellent! Happy to help! 👍️

Jennifer Melin

@Paul Newcome '

I need to put criteria on a COUNTM. I tried using the COUNTM(COLLECT. I need it to only count if assigned to specific person and within the last 7 days. Below is what I am trying.

=COUNTM(COLLECT({Branch}, [Assigned to]@row,[{sold date},>=TODAY(-7)]){Sold Products})

Paul Newcome

@Jennifer Melin There are a couple of syntax issues with your COLLECT function. Try this...

=COUNTM(COLLECT({Sold Products}, {Branch}, @cell = [Assigned to]@row, {sold date}, @cell >= TODAY(-7)))