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

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 <B999CC6F-1D56-4923-B935-11B699CF3FF0@microsoft.com>,
"Daniel Bonallack" <DanielBonallack@discussions.microsoft.com> 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

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 <B999CC6F-1D56-4923-B935-11B699CF3FF0@microsoft.com>,
> "Daniel Bonallack" <DanielBonallack@discussions.microsoft.com> 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
>