Re: Counting Problem

Giganews Newsgroups
Subject: Re: Counting Problem
Posted by:  Bob Phillips (bob.N…@googlemailxxx.com)
Date: Wed, 10 May 2006

=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" <Bec…@discussions.microsoft.com> wrote in message
news:98E0DC95-7D2C-4924-89C4-792FBDB303…@microsoft.com...
> 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

In response to

Counting Problem posted by Becks on Wed, 10 May 2006