Count number of unique entries occuring more than twice for a given criteria

Question

I have a table and would like to create a formula which would count the number of values that occur three times or more based on column B matching a criteria.

Example: If in column B value is Location1, then count how many values in column A occur more than 2 times. The answer should be 2 for Location1 -- values '4' and '7' occur more than 2 times.

could anyone help me with this formula? I would like the answer fill in another cell... for use in a larger sheet of stats.

Value Location
1 Location1
1 Location20
2 Location20
4 Location1
4 Location1
4 Location1
5 Location1
7 Location1
7 Location1
7 Location1
7 Location1

edit: Found my own solution. Used the "better" version formula found here (in the first answer).

The formula found in that link is:

=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>1)/COUNTIF(A2:A10,A2:A10&""))

I am familiar with COUNTIF and COUNTIFS, so I added another condition and used COUNTIFS.

The final solution would be

=SUMPRODUCT((COUNTIFS(A2:A12,A2:A12,B2:B12,"Location1")>2)/COUNTIF(A2:A12,A2:A12&""))

I'm not familiar with SUMPRODUCT, so the comparison of the count to ">2" feels unintuitive.

Welcome to Stack Overflow! Please edit your question to show the code you have so far. You should include at least an outline (but preferably a minimal reproducible example) of the code that you are having problems with, then we can try to help with the specific problem. You should also read How to Ask. — Toby Speight
Why would the answer be 3 for Location1? Wouldn't it be 2 because it appears more than twice for values 4 and 7? Also where do you want the output to be? Do you have a list of all Locations somewhere? Please edit your question to include/clarify this information. — ImaginaryHuman072889
Thanks, I've edited based on your comments. For the list of Locations, just assume that it's something like Location1 to Location20, and I have this list enumerated as well. — Chris

ImaginaryHuman072889 ImaginaryHuman072889 · Accepted Answer · 2018-02-16T13:59:50

Trickier solution than I expected.

Assuming your Value column only contains integers, this will work:

= SUMPRODUCT((COUNTIFS($B$2:$B$12,D2,$A$2:$A$12,
  ROW(INDEX($A:$A,MIN($A$2:$A$12)):INDEX($A:$A,MAX($A$2:$A$12))))>2)+0)

See below for working example. I changed some of your data just to show it works for multiple values and locations.

Count number of unique entries occuring more than twice for a given criteria

1 Answers