How do I count the times a number occurs in a given criteria?

W

w_aller

Example:
labels counts
216 28
68 2316
68 5
68 24
24 149

I need to derive a formula to calculate how many times a unique label occurs
for more than 60 counts. The results should be something like:
216= 0
68= 1
24= 1
How do I do it? Thanks a million if anyone could help me.
 
B

Biff

Hi!

You should extract a list of unique values from
the "label" list then the resulting formula is pretty
simple.

Select the range of cells that are "labels" including the
header.
Goto Data>Filter>Advanced filter.
Select Copy to another location.
Copy to - select where you want the new list to appear.
Select Unique records only.
OK out.

Assume the unfiltered list is in the range A2:A6 with A1
being the header. The new filtered list is in the range
D2:Dn with D1 being the header.

In E2 enter this formula and copy down as needed:

=SUMPRODUCT(--(A$2:A$6=D2),--(B$2:B$6>60))

Biff
 
B

Bob Phillips

From your data, I do not understand how you get to the results you show.
What are the rules for determining the label occurrences?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top