Countifs

[Vanda_a]

Hello all

Could anyone help count my below issue?
Thank you very much
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0-100[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100-500[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]500-1000[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
I would like to count column A that meet criteria with above table
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mango[/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Orange[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Apple
[/TD]
[TD]700
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Lower​

[/TD]
[TD]

Higher​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD]

Apple​

[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

100​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]

Orange​

[/TD]
[TD][/TD]
[TD]

300​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

100​

[/TD]
[TD]

500​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

Mango​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

500​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

500​

[/TD]
[TD]

1000​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

Apple​

[/TD]
[TD][/TD]
[TD]

1000​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Orange​

[/TD]
[TD][/TD]
[TD]

300​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Apple​

[/TD]
[TD]

700​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.