HELP: What formula should I use?

STLLunatic18

I have a data set and one of the columns is a drop down where you can choose more than one option. I am using the Countifs formula because I have multiple owners and i am looking to capture the number of goods that owner has sold. Lets say the one column is Joe, Bill, Tim, Alice and the dropdown options are Apple, Banana, Orange, Grapes, Strawberry, Pears. The formula i am using is: Countifs([Owner]:[Owner], "Joe", [Fruit]:[Fruit], "Apple"). I did that for all the fruits, but i noticed that formula does not pick up the "Fruit" that are selected multiple times in the drop down. What formula do i need to use to capture the "fruit" that have been selected more than once in a particular row.

Paul Newcome

You would need to incorporate the HAS function.

=COUNTIFS(Owner:Owner, @cell = "Joe", Fruit:Fruit, HAS(@cell, "Apple"))

STLLunatic18

Do i do that for all fruits? Meaning do i put HAS(@cell, "Apple", "Banana", "Grape" and so forth?

Paul Newcome

No. Whether or not you need to include the other fruits will depend on exactly what you are trying to count.

STLLunatic18

I have a column and it is a drop down option, so more than one choice can be selected. I am looking to get the total number for all the fruit. What would that formula look like?

Paul Newcome

So you mean all selections made for "Joe"?

You would need this instead:

=COUNTM(COLLECT(Fruit:Fruit, Owner:Owner, @cell = "Joe"))