Can I use COUNTIF and COUNTM in the same formula?

Cheryl Padgett

I am trying to count the number of selections in cells in a multi-select column (call it Column B) based on the values entered in Column A.

For example, IF Column A = "APPLE" how many total items have been selected in Column B for those rows.

Can this even be done?

I can get the value I need using COUNTM and selecting the specific cells myself, but when I need to add another row with "Apple" for example, I will have to go back and adjust my formula to include the new cell. I am trying to avoid doing that if at all possible.

Thanks to anyone who can help.

Heather Duff

Hi @Cheryl Padgett ,

I'm sure there's a way to make it work without a helper column, but the easiest way for me was to do this:

Add a "Count" column (then hide it, if desired) with the formula: =COUNTM([column b]@row)

Then in the column you want to total:

=SUM(COLLECT(Count:Count, [Column B]:[Column B], HAS(@cell, [Column A]@row)))

This translates to: Sum the Count column for only the rows where Column B contains the text that is in this row's Column A.

Here's a screenshot of what I set up:

Hope this helps! Let me know if it works for you.

Best,

Heather

Paul Newcome

You are going to want a COUNTM/COLLECT.

=COUNTM(COLLECT(ColumnB:ColumnB, ColumnA:ColumnA, CONTAINS("Apple", @cell)))

Heather Duff

Hi @Cheryl Padgett ,

I'm sure there's a way to make it work without a helper column, but the easiest way for me was to do this:

Add a "Count" column (then hide it, if desired) with the formula: =COUNTM([column b]@row)

Then in the column you want to total:

=SUM(COLLECT(Count:Count, [Column B]:[Column B], HAS(@cell, [Column A]@row)))

This translates to: Sum the Count column for only the rows where Column B contains the text that is in this row's Column A.

Here's a screenshot of what I set up:

Hope this helps! Let me know if it works for you.

Best,

Heather

Paul Newcome

@Heather D Did you give the COUNTM/COLLECT a try?

Heather Duff

@Paul Newcome Nope. I thought it out a few ways in my head, got frustrated, and went with a helper column. If you have a way to make it work, please show me! I'd love to see it in one concise formula rather than a helper column.

Paul Newcome

If you look in row 3 you will see the result is calculated correctly. Of course you may or may not want to swap out the CONTAINS for a HAS and in the above thread we are looking at a different range/criteria set for which cells to collect, but the idea is still the same.

Heather Duff

@Paul Newcome that was MUCH easier than in my head! Brilliant. 🙌

Paul Newcome

Hahaha. Your comment had me worried for a minute. I didn't test it before posting my initial comment, but that's because I had thought for sure I had used it before. I had to go back and test just to be sure before asking if you had tried it.

Cheryl Padgett

I ended up using the count column because that actually added value if someone was looking at the main sheet and I have some cells with over a dozen selections.

I tried using =COUNTM(COLLECT(ColumnB:ColumnB, ColumnA:ColumnA, CONTAINS("Apple", @cell))) and could not get it to work. I even copied the exact formula and pasted my terms into it. I may play with it later to try to figure out if I am somehow doing something wrong.

Thanks for the replies!!!

Paul Newcome

@Cheryl Padgett I'm glad you were able to get a solution that works for you. Out of curiosity, were you getting an error or an incorrect count?

@Heather D Looks like you win this one. 😜 Haha

Heather Duff

@Paul Newcome 🤣