Counif Formula bases on other column reference

[harinsh]

I have data similar to below...I would need help in getting coutif formula ...I need count based on names and it should exclude the "Zero" value lines...for each amount type...

I need one count for aaaa & bbbb and rest all other names should come under other names....

Names	Amt1	Amt2	Amt3
aaaa	1,663	1,092	-
bbbb	1,446	1,642	-
bbbb	-	1,652	1,852
dddd	2,560	1,902	1,452
dddd	1,150	1,359	1,537
aaaa	1,111	-	1,518
aaaa	-	-	1,899
fffff	-	1,821	-
fffff	1,350	1,490	1,943
Output should be below
Count	Amt1	Amt2	Amt3
aaaa & bbbb	3	3	3
Other Names	3	4	3

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[gaz_chops]

Maybe

=COUNTIFS($A$2:$A$10,"aaaa",B$2:B$10,">"&0)+COUNTIFS($A$2:$A$10,"bbbb",B$2:B$10,">"&0)
or
=SUM(COUNTIFS($A$2:$A$10,{"aaaa","bbbb"},B$2:B$10,">"&0))


and (B21 is cell with above formula in)
=COUNTIFS(B$2:B$10,">"&0)-B21

 

[Eric W]

Same idea as gaz_chops, but a shorter way to write the first formula:

=SUM(COUNTIFS($A$2:$A$10,{"aaaa","bbbb"},B$2:B$10,">0"))

 

[harinsh]

Perfect...Thanks for your quick responses ...

gaz_chops
Eric W

 

[gaz_chops]

You're welcome.