Frequency of data with criteria
 

Hi

I have the below data, with each month additional data is added to the end
of the list, so i am looking for a formula that looks at the unique sales
persons name in the first column and tells me the number of unique contracts
that have been renewed in the month. The formula I would like to be in a
master summary file. So the result I am looking for is Card would be 2,
Cooper would = 2 and Double would = 1 and this numerical result would be on a
summary sheet. Hope this makes sense, I can do a frequency formula just on
column B, however this doesn't match it to the sales person. Thanks M

CARD Rabbit contract 31-Mar-07
CARD Rabbit contract 31-Mar-07
CARD Sams Contract 31-Mar-07
CARD Sams Contract 31-Mar-07
COOPER Window Contract 31-Mar-07
COOPER Window Contract 31-Mar-07
COOPER Window Contract 31-Mar-07
COOPER Window Contract 31-Mar-07
COOPER EH Contract 31-Mar-07
COOPER EH Contract 31-Mar-07
COOPER EH Contract 31-Mar-07
DOUBLE HBS 31-Mar-07

Frequency of data with criteria
 

Assume your data is in the range A2:D13

F2:F4 = Card, Cooper, Double

Enter this array** formula in G2 and copy down to G4:

=COUNT(1/FREQUENCY(IF(A$2:A$13=F2,MATCH(B$2:B$13,B$2:B$13,0 )),ROW(A$2:A$13)-MIN(ROW(A$2))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Cinny" wrote in message
...
Hi

I have the below data, with each month additional data is added to the end
of the list, so i am looking for a formula that looks at the unique sales
persons name in the first column and tells me the number of unique
contracts
that have been renewed in the month. The formula I would like to be in a
master summary file. So the result I am looking for is Card would be 2,
Cooper would = 2 and Double would = 1 and this numerical result would be
on a
summary sheet. Hope this makes sense, I can do a frequency formula just on
column B, however this doesn't match it to the sales person. Thanks M

CARD Rabbit contract 31-Mar-07
CARD Rabbit contract 31-Mar-07
CARD Sams Contract 31-Mar-07
CARD Sams Contract 31-Mar-07
COOPER Window Contract 31-Mar-07
COOPER Window Contract 31-Mar-07
COOPER Window Contract 31-Mar-07
COOPER Window Contract 31-Mar-07
COOPER EH Contract 31-Mar-07
COOPER EH Contract 31-Mar-07
COOPER EH Contract 31-Mar-07
DOUBLE HBS 31-Mar-07