Counting Problem

Giganews Newsgroups
Subject: Counting Problem
Posted by:  Becks
Date: Wed, 10 May 2006

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:A5>0),--(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

Replies