=SUMPRODUCT(--(B1:B10="X"), --(C1:C10DATE(2009,1,16),--(C1:C10<DATE(2009,1,21)
will count how may entries have X in the B column and a date In column C
that is 16/01/2009 but less that 21/01/2009.
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.htm
However, this will count every entry that fits the criteria, not just the
unique ones. If this is required and you are prepared to add a 'helper
column', let me know and I will show you how.
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"Fiona Yorke-Saville" wrote in
message ...
Hi Bernard
I wondered if you knew if I can add another 2 conditions to this formula.
Would it be possible to count a list as detailed in your formula, but only
if coloumn B relates to a set condition and if the date is between a
certain
time.
e.g
Coloumn A Coloumn B Coloumn C
1111 X 20/01/2009
1111 X 19/01/2009
1234 Y 19/01/2009
1258 Z 15/01/2009
1254 Y 14/01/2009
I want to count Coloumn A (as detailed in your formula) if Coloumn B
xontains X and Coloumn C is greter that 16/01/2009 but less that
21/01/2009.
Is that too many formulas?
Thanks
"Bernard Liengme" wrote:
How many unique items (count) ?
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&""))
If there are blanks in the range they count as an item
=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))
blanks are note included in count
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
"Fiona" wrote in message
...
Hi
Does anyone know how I can count numbers in a list, but only count the
one's
that are different, for example, if counting the following:-
12345
12345
23569
12358
22583
I only want to show a total of 4...
Also, can this be applied to text?
thankyou