Countifs

[Vanda_a]

Hello all

Could anyone help count my below issue?
Thank you very much

	A	B
1	0-100	0
2	100-500	2
3	500-1000	2

<tbody>
</tbody>

I would like to count column A that meet criteria with above table

	A	B	C	D
1	Apple	100
2	Orange		300
3	Mango			500
4	Apple		1000
5	Orange		300
6	Apple	700

<tbody>
</tbody>

 

[Marcelo Branco]

Could you explain the logic to get the results you have shown?
0
2
2
Do you want to count unique the fruits that meet the criteria?

M.

 

[Vanda_a]

Hello
Yes count only unique fruit
Thank you very much

 

[Marcelo Branco]

Maybe something like this...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Lower​	Higher​	Result​		Apple​	100​
2​	0​	100​	0​		Orange​		300​
3​	100​	500​	2​		Mango​			500​
4​	500​	1000​	2​		Apple​		1000​
5​					Orange​		300​
6​					Apple​	700​

<tbody>
</tbody>

Array formula in C2 copied down
=SUM(IF(FREQUENCY(IF($F$1:$H$6<>"",IF($F$1:$H$6>=A2,IF($F$1:$H$6 < B2,MATCH($E$1:$E$6,$E$1:$E$6,0)))),ROW($E$1:$E$6)-ROW($E$1)+1),1))
<b2,match($e$1:$e$6,$e$1:$e$6,0)))),row($e$1:$e$6)-row($e$1)+1),1))
Ctrl+Shift+Enter

M.</b2,match($e$1:$e$6,$e$1:$e$6,0)))),row($e$1:$e$6)-row($e$1)+1),1))

 

[Vanda_a]

Yeah that is i am looking for.

You saved my day.

Thank you very much

 

[Marcelo Branco]

You are welcome. Glad to help

M.