Count + condition

[abc2002]

Hi again!

My other problem is that I want to get the number of different
variables (text type) + a condition (date)

Example:

Col 1
AAA
AAA
BBB
BBB
BBB
AAA
CCC
DDD
AAA
EEE

Col 2
2004-08-01
2004-08-01
2004-08-01
2004-08-02
2004-08-02
2004-08-04
2004-08-04
2004-08-04
2004-08-04
2004-08-04

I should get the numbers of different variables for each date

2004-08-01 -> 2
2004-08-02 -> 1
2004-08-04 -> 4


thanks

 

[Frank Kabel]

Hi
if you create the list of unique dates on a separate sheet
(e.g. using 'Data - Filter - Advanced Filter') in column
A, enter the following array formula in B1 (entered with
CTRL+SHIFT+ENTER):
=SUM(IF(FREQUENCY(IF(('sheet1!!$B$1:$B$100=A1),MATCH
('sheet1'!$A$1:$A$100,'sheet1'!$A$1:$A$100,0),""),IF
(('sheet1'!$B$1:$B$100=A1),MATCH
('sheet1'!$A$1:$A$100,'sheet1'!$A$1:$A$100,0),""))>0,1))