Sumproduct formula help

[Guest]

I have banker names in Column A

I have titles in column B (either "Director" or "Managing Director")

In column C I have market sales

In column D I have the banker's market share

So one row may be SMITH, A. Director 150.2 5.6%

To get the TOTAL market share for all 100 bankers I would do:
sumproduct(D2101*C2:C101)/sum(C2:C101)

But how would I get the total market share for just managing directors?

Hope this is clear. Thanks in advance,
Daniel

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(B2:B101="Managing Director),C2:C101,
D2101)/SUM(C2:C101)

For an explanation of "--" see

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Guest]

Thanks very much

One way:

=SUMPRODUCT(--(B2:B101="Managing Director),C2:C101,
D2101)/SUM(C2:C101)

For an explanation of "--" see

http://www.mcgimpsey.com/excel/doubleneg.html