1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • Complex Sumproduct formula

    From Ray Smart@21:1/5 to All on Mon Feb 1 21:08:57 2021
    Hi Guys,

    Hoping to leverage off all your excellent skills...

    I am trying to figure out how to do the following:

    Sheet1 - Breakdown of item by type
    Col-A,B,C,D,E
    Row
    1-Item, Total, Fee, Other, Allowables
    2-1, 45293, 21745,20610,2938
    3-2, 45612, 30287, 12366, 2959
    4-3,94517, 68329, 119732, 6455

    Sheet2 - Breakdown of item by month
    Col-A,B,C,D,E
    Row
    1-Item, Nov, Dec, Jan, Feb, Mar
    2-1, 8569, 36724, 0, 0, 0
    3-2, 0, 12583, 33030, 0, 0
    4-3,2508, 38869, 38869, 35107, 2508

    I need to understand what the revenue by type by month is and I am not getting the sumproduct to work.

    Any pointers?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Tue Feb 2 20:53:26 2021
    Hi Ray,

    Am Mon, 1 Feb 2021 21:08:57 -0800 (PST) schrieb Ray Smart:

    I am trying to figure out how to do the following:

    Sheet1 - Breakdown of item by type
    Col-A,B,C,D,E
    Row
    1-Item, Total, Fee, Other, Allowables
    2-1, 45293, 21745,20610,2938
    3-2, 45612, 30287, 12366, 2959
    4-3,94517, 68329, 119732, 6455

    Sheet2 - Breakdown of item by month
    Col-A,B,C,D,E
    Row
    1-Item, Nov, Dec, Jan, Feb, Mar
    2-1, 8569, 36724, 0, 0, 0
    3-2, 0, 12583, 33030, 0, 0
    4-3,2508, 38869, 38869, 35107, 2508

    try:
    =SUMPRODUCT((Sheet2!$A$2:$A$10=A2)*Sheet2!$B$2:$F$10)
    or
    =SUM(INDEX(Sheet2!$B$2:$F$10,MATCH(A2,Sheet2!$A$2:$A$10,0),))


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ray Smart@21:1/5 to claus...@t-online.de on Tue Feb 2 23:45:53 2021
    On Tuesday, 2 February 2021 at 23:53:29 UTC+4, claus...@t-online.de wrote:
    Hi Ray,
    Am Mon, 1 Feb 2021 21:08:57 -0800 (PST) schrieb Ray Smart:

    I am trying to figure out how to do the following:

    Sheet1 - Breakdown of item by type
    Col-A,B,C,D,E
    Row
    1-Item, Total, Fee, Other, Allowables
    2-1, 45293, 21745,20610,2938
    3-2, 45612, 30287, 12366, 2959
    4-3,94517, 68329, 119732, 6455

    Sheet2 - Breakdown of item by month
    Col-A,B,C,D,E
    Row
    1-Item, Nov, Dec, Jan, Feb, Mar
    2-1, 8569, 36724, 0, 0, 0
    3-2, 0, 12583, 33030, 0, 0
    4-3,2508, 38869, 38869, 35107, 2508
    try:
    =SUMPRODUCT((Sheet2!$A$2:$A$10=A2)*Sheet2!$B$2:$F$10)
    or
    =SUM(INDEX(Sheet2!$B$2:$F$10,MATCH(A2,Sheet2!$A$2:$A$10,0),))


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business


    Hi Claus,

    Thanks for feedback, however I already have the information that your formulas are giving me.

    I need to understand for the month of November, what my total amounts are per ItemType. Ie... In November I have a total of 11,077 across all itemTypes - but how is the 11,077 broken down across Fee, Other and Allowables given the mix in sheet 1?

    Effectively, my new table will look like:
    itemtype, Nov, Dec, Jan, etc
    Fee,?,?,?
    Other,?,?,?
    Allowable,?,?,?
    Total,11077,60021, etc

    Does this make sense?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Wed Feb 3 12:02:49 2021
    Hi Ray,

    Am Tue, 2 Feb 2021 23:45:53 -0800 (PST) schrieb Ray Smart:


    Effectively, my new table will look like:
    itemtype, Nov, Dec, Jan, etc
    Fee,?,?,?
    Other,?,?,?
    Allowable,?,?,?
    Total,11077,60021, etc

    have a look:
    https://1drv.ms/x/s!AqMiGBK2qniTgfAFx-eJlG5H2S1VOw?e=ehpuk8
    Is that the expected result?


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)