Results 1 to 2 of 2

Thread: sum ifs + index match
Thanks Thanks: 0 Likes Likes: 0

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

    Default sum ifs + index match

    Hi, I'm trying to accomplish the following results:

    January




    Publix

    2019
    2018
    2017
    Fruits


    Pear 6 10 2
    Mango - 1 3
    Banana 4
    5
    7


    Year Fruit Store January February March
    2018 Pear Publix 10 5 3
    2018 Mango Publix 1 20 2
    2019 Banana Whole Foods 10 20 30
    2019 Pear Fresh Market 1 2 3
    2019 Pear Publix 2 1 1
    2019 Pear Publix 2 1 1
    2019 Pear Publix 2 1 1
    2017 Mango Whole Foods 1 10 2
    2018 Banana Publix 5 5 10
    2017 Pear Publix 2 3 4
    2017 Mango Publix 3 3 4
    2017 Banana Publix 4 3 4
    2017 Banana Publix 2 1 1
    2019 Banana Publix 2 1 1
    2019 Banana Publix 2 1 1

    which combination of formulas do I need?

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,254
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: sum ifs + index match

    Here is one way with SUMPRODUCT. Copy formula down and across as needed.
    You may also want to look at a pivot table.

    ABCDEF
    1January
    2 Publix
    3 201920182017
    4Fruits
    5Pear6102
    6Mango013
    7Banana456
    8
    9
    10
    11YearFruitStoreJanuaryFebruaryMarch
    122018PearPublix1053
    132018MangoPublix1202
    142019BananaWhole Foods102030
    152019PearFresh Market123
    162019PearPublix211
    172019PearPublix211
    182019PearPublix211
    192017MangoWhole Foods1102
    202018BananaPublix5510
    212017PearPublix234
    222017MangoPublix334
    232017BananaPublix434
    242017BananaPublix211
    252019BananaPublix211
    262019BananaPublix211

    Spreadsheet Formulas
    CellFormula
    B5=SUMPRODUCT(($D$11:$F$11=$A$1)*($A$12:$A$26=B$3)*($B$12:$B$26=$A5)*($C$12:$C$26=$B$2)*($D$12:$F$26))


    Excel tables to the web >> Excel Jeanie HTML 4

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
  •