=SUM(--(FREQUENCY(IF((B1:B6="BP")*(C1:C6=--"2007-03-31"),MATCH(A1:A6,A1:A6,0
)),ROW(INDIRECT("1:"&ROWS(A1:A6))))0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Becks" wrote in message
...
Hi pls can you help with this. I am trying to count the number of people
on
a spreadsheet depending on certain criteria.

A B C
1 Bloggs BP 29/07/06
2 Smith BP 31/03/07
3 Smith BP 31/03/07
4 Jones HO 31/03/07
5 Dodd PE 29/09/06
6 Blank Blank Blank
I need to count the people with a date of 31/03/07, with a sperate answer
for each code, but i don't want it to count the same person twice and i
need
to ignore blanks. The answer i want from the above data is: BP = 1, HO =
1,
PE = 0. I have got somewhere near using the following
=SUMPRODUCT(--(A1:A50),--(B1:B5=BP),--(C1:C5=31/03/07)) but this counts
records which appear twice, is there any way to ignore names if they are
the
same? Ps in the formula for the date i use a cell reference.

Thanks

Becks