Sumproduct formula help
 

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(D2:D101*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

Sumproduct formula help
 

One way:

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

For an explanation of "--" see

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


In article ,
"Daniel Bonallack" wrote:

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(D2:D101*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

Sumproduct formula help
 

Thanks very much

"JE McGimpsey" wrote:

One way:

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

For an explanation of "--" see

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


In article ,
"Daniel Bonallack" wrote:

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(D2:D101*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