Count number of recurrences

[dabon]

Hi,
I'm sure that it's very easy question and there is an easy way to do that, but I don't know why now it's not in my mind.
Look the image attached. For each cell, I would use an excel formula that allows to get the values on Table2 starting from Table1. I don't want to use a pivot table.

Thanks

 

[etaf]

not sure what criteria you are using
just seems to be a unique list of column A - Value 1

as you have 2019 - no UNQUE() function
so

=IFERROR(INDEX($A$3:$A$11, MATCH(0,COUNTIF($D$2:D2, $A$3:$A$11), 0)),"")
should do that

Book2
	A	B	C	D
1
2
3	a			a
4	b			b
5	c			c
6	a
7	a
8	c
9	b
10	b
11	c
12	a
13	a
14	a
15	a
16
Sheet1

Cell Formulas
Range	Formula
D3:D13	D3	=IFERROR(INDEX($A$3:$A$15, MATCH(0,COUNTIF($D$2:D2, $A$3:$A$15), 0)),"")

 

[anand3dinesh]

Hi,
I'm sure that it's very easy question and there is an easy way to do that, but I don't know why now it's not in my mind.
Look the image attached. For each cell, I would use an excel formula that allows to get the values on Table2 starting from Table1. I don't want to use a pivot table.

Thanks

I believe this is what you after

assuming points below
Table1 data is in column A and B
Table2 data is in column D and row 1

insert below formula in E1 cell

=COUNTIFS($A$1:$A$8,$D2,$B$1:$B$8,E$1)

 

[etaf]

ignore my post, as you seem to have edited you requirement - after i posted - makes more sense now

anand3dinesh​

has a solution that seems to work ok

Book2
	A	B	C	D	E	F	G
1
2	a	n			n	s	x
3	b	s		a	4	2	1
4	c	x		b	1	2	0
5	a	n		c	0	0	3
6	a	s
7	c	x
8	b	n
9	b	s
10	c	x
11	a	n
12	a	s
13	a	x
14	a	n
15
Sheet1

Cell Formulas
Range	Formula
D2	D2	=IFERROR(INDEX($A$2:$A$14, MATCH(0,COUNTIF(#REF!, $A$2:$A$14), 0)),"")
E3:G5	E3	=COUNTIFS($A$1:$A$14,$D3,$B$1:$B$14,E$2)
D7	D7	=IFERROR(INDEX($A$2:$A$14, MATCH(0,COUNTIF($D$2:D5, $A$2:$A$14), 0)),"")
D8:D13	D8	=IFERROR(INDEX($A$2:$A$14, MATCH(0,COUNTIF($D$2:D7, $A$2:$A$14), 0)),"")

 

[dabon]

Thanks to all. I was not using the COUNTIFS formula in correct way.

Thanks