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,

Apple	XX
Pear	XX
Orange	XX
Grape	XX
Peach	XX

<tbody>
</tbody>

On B,

Name	Apple	Pear	Orange	Grape	Peach
Jack	1	0	0	1	1
Peter	2	1	2	0	0
Daniel	1	4	0	2	1
John	2	1	1	1	1
David	1	2	0	0	0

<tbody>
</tbody>

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