COUNTIFS with index match/vlookup

[Yoshiaki]

Hi all,

first time in this forum, hope that you could help me.


I have 2 sheets "A" and "B".

on A,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Apple[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD]XX[/TD]
[/TR]
</tbody>[/TABLE]


On B,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Apple[/TD]
[TD]Pear[/TD]
[TD]Orange[/TD]
[TD]Grape[/TD]
[TD]Peach[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Daniel[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Is there any countifs + index match formula which i could write on XX on Sheet A, which will count the number people with Apple,Pear...(not SUM of apples) from Sheet B?
*I want something like Countifs(Apple,">0") but with a reference so that next time i could just modify/add the fruit name on Sheet A and it will automatically find from Sheet B.


Thanks!
Yoshi

 

[Aladin Akyurek]

Name A1:F6 of B DATA.

In B2 of A enter and copy down:

=SUMIFS(INDEX(DATA,0,MATCH($A2,INDEX(DATA,1,0),0)),INDEX(DATA,0,1),$A2)

 

[Marcílio_Lobão]

Yoshi, Good morning.

Try to use it:

Sheet 1 --> B2 -->

=SUMPRODUCT((SHEET2!$B$1:$F$1=A2)*(SHEET2!$B$2:$F$6>0))

Please, tell us if it worked for you.
I hope it helps.

 

[Aladin Akyurek]

Name A1:F6 of B DATA.

In B2 of A enter and copy down:

=SUMIFS(INDEX(DATA,0,MATCH($A2,INDEX(DATA,1,0),0)),INDEX(DATA,0,1),$A2)

If you just want to count the records where > 0, then:

=COUNTIFS(INDEX(DATA,0,MATCH($A2,INDEX(DATA,1,0),0)),">0",INDEX(DATA,0,1),$A2)

 

[Yoshiaki]

Thanks all!!!! My problem is solved