Results 1 to 9 of 9

Thread: Lookup table array and sum all rows that are applicable
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lookup table array and sum all rows that are applicable

    Hello,

    I want to sum all "Apples" under the "Jan" Column in the following table. I want to make the formula dynamic by having the formula lookup the entire table and select the correct columns and rows rather than using "Sumifs" on a per column basis. Any help will be appreciated.

    The formula should add the items in RED.
    Jan Feb Mar
    Apples 1 6 11
    Oranges 6 11 16
    Apples 11 16 21
    Lemons 16 21 26

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,805
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Lookup table array and sum all rows that are applicable

    with PowerQuery unpivot columns
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Fruits"}, "Attribute", "Value")
    in
        #"Unpivoted Other Columns"
    then use PivotTable

    Fruits Jan Feb Mar Fruits Apples
    Apples
    1
    6
    11
    Oranges
    6
    11
    16
    Attribute Sum of Value
    Apples
    11
    16
    21
    Jan
    12
    Lemons
    16
    21
    26
    Feb
    22
    Mar
    32
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Lookup table array and sum all rows that are applicable

    Try this formula

    Hoja24

     ABCDEFGHI
    1FRUITJanFebMar  FruitMonthSum
    2Apples1611  ApplesJan12
    3Oranges61116     
    4Apples111621     
    5Lemons162126     

    Formula
    CellFormula
    I2=SUMPRODUCT((Tabla1[FRUIT]=G2)*(Tabla1[[#Headers],[Jan]:[Mar]]=H2)*(Tabla1[[Jan]:[Mar]]))


    Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
    Regards Dante Amor

  4. #4
    New Member
    Join Date
    Mar 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup table array and sum all rows that are applicable

    Quote Originally Posted by sandy666 View Post
    with PowerQuery unpivot columns
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Fruits"}, "Attribute", "Value")
    in
        #"Unpivoted Other Columns"
    then use PivotTable

    [COLOR=#FFFFFF ]Fruits[/COLOR] [COLOR=#FFFFFF ]Jan[/COLOR] [COLOR=#FFFFFF ]Feb[/COLOR] [COLOR=#FFFFFF ]Mar[/COLOR] Fruits Apples
    Apples
    1
    6
    11
    Oranges
    6
    11
    16
    Attribute Sum of Value
    Apples
    11
    16
    21
    Jan
    12
    Lemons
    16
    21
    26
    Feb
    22
    Mar
    32

    Thanks Was hoping for a formula solution. But will keep this in mind...thanks again!!

  5. #5
    New Member
    Join Date
    Mar 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup table array and sum all rows that are applicable

    Thanks! I tried this and get a
    #VALUE ! Error. The formula I have is "=SUMPRODUCT(Table1[Fruit]=G2)*(Table1[[#Headers],[Jan]:[Mar]]=H2*(Table1[[Jan]:[Mar]])". Does it work for you?

  6. #6
    New Member
    Join Date
    Mar 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup table array and sum all rows that are applicable

    Thanks Dante! I tried this and get a
    #VALUE ! Error. The formula I have is "=SUMPRODUCT(Table1[Fruit]=G2)*(Table1[[#Headers],[Jan]:[Mar]]=H2*(Table1[[Jan]:[Mar]])". Does it work for you?

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Lookup table array and sum all rows that are applicable

    Regards Dante Amor

  8. #8
    New Member
    Join Date
    Mar 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup table array and sum all rows that are applicable

    Perfect! Works like a charm. Thanks so much Dante!

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Lookup table array and sum all rows that are applicable

    I'm glad to help you. I appreciate your kind comments.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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