+ Reply to Thread
Results 1 to 3 of 3

Sumproduct formula help

Hybrid View

  1. #1
    Daniel Bonallack
    Guest

    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

  2. #2
    JE McGimpsey
    Guest

    Re: 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 <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


  3. #3
    Daniel Bonallack
    Guest

    Re: 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 <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

    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1