# Thread: Index Match, sum if or vlookup? Thanks:  4 Post #5330031 (1)Post #5328730 (1)Post #5328736 (1)Post #5328775 (1) Likes: 0

1. ## 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. ## 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))

3. ## 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

```

4. ## 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.

 A B C D E F G H I J K L M N 1 Year to date 2 Yellow Florida 3 April 37 37 37 4 5 January February March April May Jun July August September October November December 6 Florida Blue 5 5 5 7 5 8 1 7 Florida Yellow 10 5 5 17 12 15 2 8 California Green 10 10 5 5 16 16 3 9 New York Yellow 2 2 2 2 3 4 4

 Cell Formula 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

5. ## 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. ## 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?

7. ## 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. ## 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))

9. ## 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"

 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. ## Re: Index Match, sum if or vlookup?

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.