software

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:A5gt;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


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$6lt;gt;quot;quot;)/COUNTIF($A$1:$A$6,$A$1:$A$6amp;quot;quot;)*($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--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=540603
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$6lt;gt;quot;quot;)/COUNTIF($A$1:$A$6,$A$1:$A$6amp;quot;quot;)*($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--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=540603=SUM(--(FREQUENCY(IF((B1:B6=quot;BPquot;)*(C1:C6=--quot;2007-03-31quot;),MATCH(A1:A6,A1:A6,0
)),ROW(INDIRECT(quot;1:quot;amp;ROWS(A1:A6))))gt;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)

quot;Becksquot; gt; wrote in message
...
gt; Hi pls can you help with this. I am trying to count the number of people
on
gt; a spreadsheet depending on certain criteria.
gt;
gt; A B C
gt; 1 Bloggs BP 29/07/06
gt; 2 Smith BP 31/03/07
gt; 3 Smith BP 31/03/07
gt; 4 Jones HO 31/03/07
gt; 5 Dodd PE 29/09/06
gt; 6 Blank Blank Blank
gt; I need to count the people with a date of 31/03/07, with a sperate answer
gt; for each code, but i don't want it to count the same person twice and i
need
gt; to ignore blanks. The answer i want from the above data is: BP = 1, HO =
1,
gt; PE = 0. I have got somewhere near using the following
gt; =SUMPRODUCT(--(A1:A5gt;0),--(B1:B5=BP),--(C1:C5=31/03/07)) but this counts
gt; records which appear twice, is there any way to ignore names if they are
the
gt; same? Ps in the formula for the date i use a cell reference.
gt;
gt; Thanks
gt;
gt; Becks
Thanks Bob your a star!

Becks

quot;Bob Phillipsquot; wrote:

gt; =SUM(--(FREQUENCY(IF((B1:B6=quot;BPquot;)*(C1:C6=--quot;2007-03-31quot;),MATCH(A1:A6,A1:A6,0
gt; )),ROW(INDIRECT(quot;1:quot;amp;ROWS(A1:A6))))gt;0))
gt;
gt; which is an array formula, it should be committed with Ctrl-Shift-Enter, not
gt; just Enter.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;Becksquot; gt; wrote in message
gt; ...
gt; gt; Hi pls can you help with this. I am trying to count the number of people
gt; on
gt; gt; a spreadsheet depending on certain criteria.
gt; gt;
gt; gt; A B C
gt; gt; 1 Bloggs BP 29/07/06
gt; gt; 2 Smith BP 31/03/07
gt; gt; 3 Smith BP 31/03/07
gt; gt; 4 Jones HO 31/03/07
gt; gt; 5 Dodd PE 29/09/06
gt; gt; 6 Blank Blank Blank
gt; gt; I need to count the people with a date of 31/03/07, with a sperate answer
gt; gt; for each code, but i don't want it to count the same person twice and i
gt; need
gt; gt; to ignore blanks. The answer i want from the above data is: BP = 1, HO =
gt; 1,
gt; gt; PE = 0. I have got somewhere near using the following
gt; gt; =SUMPRODUCT(--(A1:A5gt;0),--(B1:B5=BP),--(C1:C5=31/03/07)) but this counts
gt; gt; records which appear twice, is there any way to ignore names if they are
gt; the
gt; gt; same? Ps in the formula for the date i use a cell reference.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Becks
gt;
gt;
gt;