Count Multiple items within a single cell - Countif ?

Yuan Wong

Hi morning everyone,

Just recently started using Smartsheet platform for a project. Not sure I am doing this correctly or if there is an alternative solution.

I am trying to count the number of rows which contains the provinces populated by a dropdown list (which has multiple input criteria ( AB, BC, MB, NB, ON, etc,)

I have used both OR(@cell= ) and HAS(@cell) features, but this only returns counts either a single item in a cell or just not counting properly without properly adding ALL cells as reference.

just testing it on this sheet, but will reference it to another sheet later, which has FORMS created.

is there a way to count multiple items within a SINGLE cell once populated without referencing ALL the @cell references? in my case (provinces)

count doesn't seem to be correct?

=COUNTIF($Location$17:Location23, OR(@cell = Location@row, @cell = Location2, @cell = Location4, @cell = Location6, @cell = Location5, @cell = Location7, @cell = Location8, @cell = Location9, @cell = Location10, @cell = Location11, @cell = Location12, @cell = Location13, @cell = Location14))

any suggestions or ideas?

thank you!

Paul Newcome

I would suggest

=COUNTIFS($Location$17:$Location$23, CONTAINS(Location@row, @cell))

You can put this in rows 2 - 14 and it should give you the count for each.

Paul Newcome

Sure thing!

I use both depending on what mood I am in when building. Sometimes I use the helper, and sometimes I use the single formula. It also depends on exactly how big that single formula is going to be.

I was also thinking... For the single formula, couldn't we count how many have "Government" and add that to how many have "NATIONAL", then subtract from that how many have both? That would shrink it down a little bit because two of the COUNTIFS would only looking at a single column instead of both.

=COUNTIFS(Customer:Customer, CONTAINS("Government", @cell), Choice:Choice, Stats@row) + COUNTIFS(Location:Location, HAS(@cell, "NATIONAL), Choice:Choice, Stats@row) - COUNTIFS(Customer:Customer, CONTAINS("Government", @cell), Location:Location, HAS(@cell, "NATIONAL"), Choice:Choice, Stats@row)

It isn't A LOT shorter because we only removed a total of 2 range/criteria sets from the whole thing, but it also lets us forget about the NOT functions which gets rid of a couple of those pesky parenthesis. I think overall it is only about 100 characters removed.

I do think though that the helper column with the IF statement and a basic

=SUMIFS([sum2]:[sum2], Choice:Choice, Stats@row)

to get your total is going to be the least amount of work now that we have the column formulas to very quickly populate every row with the IF.

Paul Newcome

I would suggest

=COUNTIFS($Location$17:$Location$23, CONTAINS(Location@row, @cell))

You can put this in rows 2 - 14 and it should give you the count for each.

Yuan Wong

Thank you @Paul Newcome !!🌟

that seems to have done the trick!

Paul Newcome

Happy to help. 👍️

Yuan Wong

Hi @Paul Newcome ,

sorry to bother you again.

I have another question/problem and wonder if you can please assist.

Is there a way to determine count for TWO columns if the values holds true (see below)?

I tried a few methods with IF statements, but couldn't get it to work. I used a "trick" to count individual columns - [CheckCustomer] and [CheckLocation], then sum both of them with IF statement. This method works, but just wondering if there is a BETTER solution?

so basically, I need it to count column [Customer] if it Contains("Government",@cell) AND [Location] if it Contains("National",@cell) (without counting it TWICE, but should be counted as "1") and also count if [Choice] Contains(Stats@row,@cell) *which are Won, lost or unknown*

my current solution:

=COUNTIFS(Choice7:Choice13, CONTAINS(Stats@row, @cell), [sum2]7:[sum2]13, HAS(@cell, Stats5))

thank you.

Genevieve P.

Hi @Yuan Wong

If I'm understanding you correctly, you want to count the following: if the Customer contains "Government" OR if the Location is "National", but you don't want duplicate counts for when both of these criteria are met, is that correct?

Paul is a formula wizard and may have a more succinct way of doing this, but what I would do is create 3 different COUNTIFS statements, then add them all together in one long formula.

1) The first COUNTIFS would count the rows where all three criteria are met (where both the Location and the Customer have your criteria).

2) The second COUNTIFS would count where Customer contains "Government", but where the Location is not "National".

3) The final COUNTIFS would count where the Location has "National", but the Customer is not "Government".

Then of course we also want to include that your "Choice" column contains the criteria in your Stats column in each of these COUNTIFS as well.

Try this:

=COUNTIFS(Customer:Customer, CONTAINS("Government", @cell), Location:Location, HAS(@cell, "National"), Choice:Choice, Stats@row) + COUNTIFS(Customer:Customer, CONTAINS("Government", @cell), Location:Location, NOT(HAS(@cell, "National")), Choice:Choice, Stats@row) + COUNTIFS(Customer:Customer, NOT(CONTAINS("Government", @cell)), Location:Location, HAS(@cell, "National"), Choice:Choice, Stats@row)

Let me know if this works for you!

Cheers,

Genevieve

Paul Newcome

@Yuan Wong

I think you should be able to use

=COUNTIFS(Customer:Customer, CONTAINS("Government", @cell), Location:Location, HAS(@cell, "NATIONAL"), Choice:Choice, Stats@row)

Genevieve P.

@Paul Newcome

Wouldn't that only count the rows that have both criteria? (Versus one or the other or both)

Paul Newcome

@Genevieve P Yes. That's how I read this:

"so basically, I need it to count column [Customer] if it Contains("Government",@cell) AND [Location] if it Contains("National",@cell) (without counting it TWICE, but should be counted as "1") and also count if [Choice] Contains(Stats@row,@cell) *which are Won, lost or unknown*"

Customer contains "Govenrment", Location contains "National", and Choice contains Stats@row counts once since it is all lumped into the same COUNTIFS.

Or am I misunderstanding what we are trying to do? I am just getting started for the day so I haven't finished off that first cup of Starbucks yet. Haha

Genevieve P.

@Paul Newcome That's what I initially thought as well! But if you look at the screen capture, there are 3 helper columns: one to only check the Customer, one to only check the Location, and then one to look at these helper columns and identify if one-or-the-other was selected, but not duplicating it (so if both are selected it still shows 1 and not 2).

I'm on to my second cup of coffee so the jitters could be affecting my thought process... ☕️🤪

@Yuan Wong can you clarify what you're looking to COUNT?

Paul Newcome

@Genevieve P I see what you mean. In that case, couldn't we just sum the [sum2] column? We could also get rid of the other two helper columns by putting this into the [sum2] column.

=IF(OR(CONTAINS("Government", Customer@row), HAS(Location@row, "NATIONAL")), 1, 0)

Genevieve P.

YES! That's what I was looking for. It still requires one helper column, but that's better than 3 and better than my massive 3x formula. Thanks Paul!

Paul Newcome

Sure thing!

I use both depending on what mood I am in when building. Sometimes I use the helper, and sometimes I use the single formula. It also depends on exactly how big that single formula is going to be.

I was also thinking... For the single formula, couldn't we count how many have "Government" and add that to how many have "NATIONAL", then subtract from that how many have both? That would shrink it down a little bit because two of the COUNTIFS would only looking at a single column instead of both.

=COUNTIFS(Customer:Customer, CONTAINS("Government", @cell), Choice:Choice, Stats@row) + COUNTIFS(Location:Location, HAS(@cell, "NATIONAL), Choice:Choice, Stats@row) - COUNTIFS(Customer:Customer, CONTAINS("Government", @cell), Location:Location, HAS(@cell, "NATIONAL"), Choice:Choice, Stats@row)

It isn't A LOT shorter because we only removed a total of 2 range/criteria sets from the whole thing, but it also lets us forget about the NOT functions which gets rid of a couple of those pesky parenthesis. I think overall it is only about 100 characters removed.

I do think though that the helper column with the IF statement and a basic

=SUMIFS([sum2]:[sum2], Choice:Choice, Stats@row)

to get your total is going to be the least amount of work now that we have the column formulas to very quickly populate every row with the IF.

Yuan Wong

I would first like to Thank BOTH of you for your thoughts and suggestions for this, thank you @Genevieve P and thank you @Paul Newcome !

sorry was a busy morning just got to this.

Genevieve's suggestion seems to be produce the same result as what Paul's suggestion for the counts and subtraction seems to be counting BOTH times for "government" and "National", but its quite interesting method :) (**below photo column on right for results shown**)

=COUNTIFS(Customer:Customer, CONTAINS("Government", @cell), Location:Location, HAS(@cell, "National"), Choice:Choice, Stats@row) + COUNTIFS(Customer:Customer, CONTAINS("Government", @cell), Location:Location, NOT(HAS(@cell, "National")), Choice:Choice, Stats@row) + COUNTIFS(Customer:Customer, NOT(CONTAINS("Government", @cell)), Location:Location, HAS(@cell, "National"), Choice:Choice, Stats@row)

similar result with Paul's suggestion, missing a " at the end of NATIONAL

=COUNTIFS(Customer:Customer, CONTAINS("Government", @cell), Choice:Choice, Stats@row) + COUNTIFS(Location:Location, HAS(@cell, "NATIONAL"), Choice:Choice, Stats@row) - COUNTIFS(Customer:Customer, CONTAINS("Government", @cell), Location:Location, HAS(@cell, "NATIONAL"), Choice:Choice, Stats@row)

-----------------------------------------------------------------------------------------------------------------------

As for your other suggestion it seems like a better idea by getting rid of my TWO helper columns currently, so thank you! I will use this!

Solution:

use IF statement on the two column Customer and Location

=IF(OR(CONTAINS("Government", Customer@row), HAS(Location@row, "NATIONAL")), 1, 0)

then using the above helper column with a SUMIF statement

=SUMIFS(sumHelper:sumHelper, Choice:Choice, Stats@row)

thank you again to both of you! Much appreciated!

Paul Newcome

@Yuan Wong Those are some strange results from both mine and Genevieve's single formula solutions. I have manually counted a few different ways and can't get them to line up with what is in the screenshot at all.

Either way... We now have a working solution, and that's what matters. Glad we were able to help. 👍️

Genevieve P.

Yes I agree with Paul, but very glad you now have a working solution that can get rid of a couple helper-columns! 🙂