Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Index Match, sum if or vlookup?

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

    Default Index Match, sum if or vlookup?


    Looking for the following resuts:


    Year to date
    Blue Florida
    April 20
    January February March April
    Florida Blue 5 5 5 5
    Florida Yellow 10 5 5 10
    California Green 10 10 5 5
    New York Yellow 2 2 2 2

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,727
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Index Match, sum if or vlookup?

    Try:

    ABCDEFGHIJKLMN
    1
    2Year to date
    3BlueFlorida
    4April20
    5
    6
    7
    8
    9JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
    10FloridaBlue5555
    11FloridaYellow105510
    12CaliforniaGreen101055
    13New YorkYellow2222

    Sheet3



    Worksheet Formulas
    CellFormula
    A4=LOOKUP(9+E307,C10:N10,C9:N9)
    B4=SUM(INDEX(C10:N13,AGGREGATE(15,6,(ROW(A10:A13)-ROW(A10)+1)/((A10:A13=B3)*(B10:B13=A3)),1),0))

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,890
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Index Match, sum if or vlookup?

    Another way

    =SUM(OFFSET($C$1,MATCH($B$10&$A$10,$A$2:$A$5&$B$2:$B$5,0),,,MATCH($A$11,$C$1:$F$1,0)))
    Enter as an array with Ctrl, Shift & Enter

    Code:
    January February March April
    Florida Blue 5 5 5 5
    Florida Yellow 10 5 5 10
    California Green 10 10 5 5
    New York Yellow 2 2 2 2
    Blue Florida
    April 20
    Last edited by gaz_chops; Aug 20th, 2019 at 12:44 PM. Reason: Amended formula
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,967
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Index Match, sum if or vlookup?

    I show you 3 other alternatives, assuming that you are going to capture the month in cell A3 and that you can have values ​​from January to December.


     ABCDEFGHIJKLMN
    1Year to date             
    2YellowFlorida            
    3April373737          
    4              
    5  JanuaryFebruaryMarchAprilMayJunJulyAugustSeptemberOctoberNovemberDecember
    6FloridaBlue5557581     
    7FloridaYellow10551712152     
    8CaliforniaGreen10105516163     
    9New YorkYellow2222344     

    CellFormula
    B3{=SUM(INDEX(C6:C9,MATCH(B2&A2,A6:A9&B6:B9,0)):INDEX(C6:N9,MATCH(B2&A2,A6:A9&B6:B9,0),MATCH(A3,C5:N5,0)))}
    C3=SUM(OFFSET(B5,SUMPRODUCT((A6:A9=B2)*(B6:B9=A2)*ROW(C6:C9))-ROW(B5),COLUMN(B5)-1,1,SUMPRODUCT((C5:N5=A3)*COLUMN(C5:N5))-COLUMN(B5)))
    D3=SUM(INDEX(C1:C9,SUMPRODUCT((A6:A9=B2)*(B6:B9=A2)*(ROW(A6:A9)))):INDEX(C1:N9,SUMPRODUCT((A6:A9=B2)*(B6:B9=A2)*(ROW(A6:A9 ))),MATCH(A3,C5:N5,0)))


    The formula in cell B3 is an array formula.

    Array formulas
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Regards Dante Amor

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

    Default Re: Index Match, sum if or vlookup?

    Thank you so much for all your options. I stayed with the first formula you gave me, it was the easiest one. Now, I realize that I need to add one more criteria. Let's suppose it is year. How can I adjust your formula to give me the Year to date results but looking at year, state, color, month.
    Thank you,
    Pripr

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,727
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Index Match, sum if or vlookup?

    What does your sheet look like? Where is the year? Is it part of the Month heading line? Do your months continue to the right, or do you have more rows?
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

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

    Default Re: Index Match, sum if or vlookup?

    Year to date
    2018 Florida Yellow
    February 15
    January February March April May June July August September October
    2018 Florida Blue 5 5 5 5
    2019 Florida Yellow 10 5 5 10
    2017 Florida Blue 5 2 2 2
    2018 Florida Yellow 1 1 1 2
    2017 New York Blue 3 1 1 1
    2019 California Green 10 10 5 5
    2018 New York Yellow 2 2 2 2

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,727
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Index Match, sum if or vlookup?

    Try this:

    ABCDEFGHIJKLMNO
    1
    2Year to date
    32018BlueFlorida
    4April20
    5
    6
    7
    8
    9JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
    102018FloridaBlue5555
    112019FloridaYellow105510
    122017FloridaBlue5222
    132018FloridaYellow1112
    142017New YorkBlue3111
    152019CaliforniaGreen101055
    162018New YorkYellow2222
    17

    Sheet3



    Worksheet Formulas
    CellFormula
    B4=LOOKUP(9+E307,D10:O10,D9:O9)
    C4=SUM(INDEX(D10:O150,AGGREGATE(15,6,(ROW(B10:B150)-ROW(B10)+1)/((A10:A150=A3)*(B10:B150=C3)*(C10:C150=B3)),1),0))

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

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

    Default Re: Index Match, sum if or vlookup?

    Dante,

    Hep me adjust the first formula you created to give me the following

    I’m trying to accomplish the following: (my formula will be based on a specific month, year, program name and look at a program description to give me the year to date total.) I will have several lines with the same
    year, same program and same program description looking for the Year to date total for "February"


    Please use this first table:
    Year to date
    2018 New Jersey Pink
    February 12
    Period 1 Period 2 Period 3 January February March
    2018 New Jersey Pink 5 5 5 5 10 15
    2018 New Jersey Pink 1 1 1 1 2 3
    2019 Virginia Yellow 10 5 5 10 15 20
    2017 Virginia Blue 5 2 2 5 7 9
    2018 Virginia Yellow 1 1 1 1 2 3
    2019 Virginia Yellow 2 2 2 2 4 6

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,217
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Index Match, sum if or vlookup?

    Cross posted https://chandoo.org/forum/threads/he...vlookup.42405/

    While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
    This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •