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

[TABLE="width: 352"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Names[/TD]
[TD]Amt1[/TD]
[TD]Amt2[/TD]
[TD]Amt3[/TD]
[/TR]
[TR]
[TD]aaaa[/TD]
[TD] 1,663[/TD]
[TD] 1,092[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]bbbb[/TD]
[TD] 1,446[/TD]
[TD] 1,642[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]bbbb[/TD]
[TD] - [/TD]
[TD] 1,652[/TD]
[TD] 1,852[/TD]
[/TR]
[TR]
[TD]dddd[/TD]
[TD] 2,560[/TD]
[TD] 1,902[/TD]
[TD] 1,452[/TD]
[/TR]
[TR]
[TD]dddd[/TD]
[TD] 1,150[/TD]
[TD] 1,359[/TD]
[TD] 1,537[/TD]
[/TR]
[TR]
[TD]aaaa[/TD]
[TD] 1,111[/TD]
[TD] - [/TD]
[TD] 1,518[/TD]
[/TR]
[TR]
[TD]aaaa[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 1,899[/TD]
[/TR]
[TR]
[TD]fffff[/TD]
[TD] - [/TD]
[TD] 1,821[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]fffff[/TD]
[TD] 1,350[/TD]
[TD] 1,490[/TD]
[TD] 1,943[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]
Output should be below[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Count[/TD]
[TD]Amt1[/TD]
[TD]Amt2[/TD]
[TD]Amt3[/TD]
[/TR]
[TR]
[TD]aaaa & bbbb[/TD]
[TD] 3[/TD]
[TD] 3[/TD]
[TD] 3[/TD]
[/TR]
[TR]
[TD]Other Names[/TD]
[TD] 3[/TD]
[TD] 4[/TD]
[TD] 3[/TD]
[/TR]
</tbody>[/TABLE]

 

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