- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel.misc
- 2006 May
- Re: Counting Problem

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

- Re: Counting Problem posted by Becks on Thu, 11 May 2006

Counting Problem posted by Becks on Wed, 10 May 2006