Multiple countif lookup

[Guest]

Ok here goes, I have a table that has a salespersons name in column A (rows
2-62) and a profit value in column G (rows 2-62 also). I want to count only
the specific salespersons name and values greater than 0 and return a
percentage figure.
For example salesman A has sold 22 cars (22 seperate entries in column A)
and on 11 of these he has sold accessories (11 >0 entries and 11 =0 entries
in column G). I need a formulae that will return 50% (or 0.50) by counting
the number of entries greater than 0 in column G and dividing it by the
number of times salesman A appears in column A.
HELP PLEASE.

 

[Dave Peterson]

=SUMPRODUCT(--(A1:A100="bruce"),--(ISNUMBER(G1:G100)),--(G1:G100>0))
/COUNTIF(A1:A100,"Bruce")

(all one cell)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Guest]

Superb. Thanks a lot.

=SUMPRODUCT(--(A1:A100="bruce"),--(ISNUMBER(G1:G100)),--(G1:G100>0))
/COUNTIF(A1:A100,"Bruce")

(all one cell)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html