Counting Problem

[Becks]

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

[Dav]

This if kind of painful and i adapted it from another post for something i did for myself, but it seems to work

=SUMPRODUCT(($A$1:$A$6<>"")/COUNTIF($A$1:$A$6,$A$1:$A$6&"")*($B$1:$B$6=G2)*($C$1:$C$6=$H$1))

where h1 contains the date
g2 contains HO

I am assuming the issue of duplicates applies to the name, hence it is looking at column A

Regards

Dav

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Becks]

Thanks Bob your a star!

Becks

"Bob Phillips" wrote:

> =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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>