Count + condition

  • Thread starter Thread starter abc2002
  • Start date Start date
A

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
 
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))
 
Back
Top