Index Match, sum if or vlookup?

P

Pripri

New Member
Joined
Aug 18, 2019
Messages
24
[TABLE="width: 336"]
<colgroup><col width="64" style="width: 48pt;" span="7"> <tbody>[TR]
[TD="width: 128, bgcolor: transparent, colspan: 2"]
Looking for the following resuts:


Year to date
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]April[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent"]February[/TD]
[TD="bgcolor: transparent"]March[/TD]
[TD="bgcolor: transparent"]April[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]California[/TD]
[TD="bgcolor: transparent"]Green[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Try:


Book1
ABCDEFGHIJKLMN
1
2Year to date
3BlueFlorida
4April20
5
6
7
8
9JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
10FloridaBlue5555
11FloridaYellow105510
12CaliforniaGreen101055
13New YorkYellow2222
Sheet3
Cell Formulas
RangeFormula
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))
 
Upvote 0
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: 
[TABLE="width: 522"]
<tbody>[TR]
[TD="class: xl63, width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="class: xl64, width: 87"]January[/TD]
[TD="class: xl64, width: 87"]February[/TD]
[TD="class: xl64, width: 87"]March[/TD]
[TD="class: xl64, width: 87"]April[/TD]
[/TR]
[TR]
[TD="class: xl64"]Florida[/TD]
[TD="class: xl64"]Blue[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl64"]Florida[/TD]
[TD="class: xl64"]Yellow[/TD]
[TD="class: xl64, align: right"]10[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl64"]California[/TD]
[TD="class: xl64"]Green[/TD]
[TD="class: xl64, align: right"]10[/TD]
[TD="class: xl64, align: right"]10[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl64"]New York[/TD]
[TD="class: xl64"]Yellow[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]Blue[/TD]
[TD="class: xl64"]Florida[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl64"]April[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:96.95px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Year to date</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Yellow</td><td >Florida</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffc000; ">April</td><td style="text-align:right; ">37</td><td style="text-align:right; ">37</td><td style="text-align:right; ">37</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="background-color:#92d050; text-align:center; ">January</td><td style="background-color:#92d050; text-align:center; ">February</td><td style="background-color:#92d050; text-align:center; ">March</td><td style="background-color:#92d050; text-align:center; ">April</td><td style="background-color:#92d050; text-align:center; ">May</td><td style="background-color:#92d050; text-align:center; ">Jun</td><td style="background-color:#92d050; text-align:center; ">July</td><td style="background-color:#92d050; text-align:center; ">August</td><td style="background-color:#92d050; text-align:center; ">September</td><td style="background-color:#92d050; text-align:center; ">October</td><td style="background-color:#92d050; text-align:center; ">November</td><td style="background-color:#92d050; text-align:center; ">December</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Florida</td><td >Blue</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td><td style="text-align:right; ">7</td><td style="text-align:right; ">5</td><td style="text-align:right; ">8</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Florida</td><td >Yellow</td><td style="text-align:right; ">10</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td><td style="text-align:right; ">17</td><td style="text-align:right; ">12</td><td style="text-align:right; ">15</td><td style="text-align:right; ">2</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >California</td><td >Green</td><td style="text-align:right; ">10</td><td style="text-align:right; ">10</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td><td style="text-align:right; ">16</td><td style="text-align:right; ">16</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >New York</td><td >Yellow</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B3</td><td >{=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)))}</td></tr><tr><td >C3</td><td >=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)))</td></tr><tr><td >D3</td><td >=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)))</td></tr></table></td></tr></table>

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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
[TABLE="width: 624"]
<colgroup><col width="64" style="width: 48pt;" span="13"> <tbody>[TR]
[TD="width: 192, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D6DCE4]#D6DCE4[/URL] , colspan: 3"]Year to date[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2018[/TD]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]February[/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent"]February[/TD]
[TD="bgcolor: transparent"]March[/TD]
[TD="bgcolor: transparent"]April[/TD]
[TD="bgcolor: transparent"]May[/TD]
[TD="bgcolor: transparent"]June[/TD]
[TD="bgcolor: transparent"]July[/TD]
[TD="bgcolor: transparent"]August[/TD]
[TD="bgcolor: transparent"]September[/TD]
[TD="bgcolor: transparent"]October[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2018[/TD]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2019[/TD]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2017[/TD]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2018[/TD]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2017[/TD]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2019[/TD]
[TD="bgcolor: transparent"]California[/TD]
[TD="bgcolor: transparent"]Green[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2018[/TD]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try this:


Book1
ABCDEFGHIJKLMNO
1
2Year to date
32018BlueFlorida
4April20
5
6
7
8
9JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
102018FloridaBlue5555
112019FloridaYellow105510
122017FloridaBlue5222
132018FloridaYellow1112
142017New YorkBlue3111
152019CaliforniaGreen101055
162018New YorkYellow2222
17
Sheet3
Cell Formulas
RangeFormula
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))
 
Upvote 0
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:
[TABLE="width: 691"]
<colgroup><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="153" style="width: 115pt; mso-width-source: userset; mso-width-alt: 5595;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;"> <col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="64" style="width: 48pt;"> <col width="85" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3108;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <tbody>[TR]
[TD="width: 304, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FCE4D6]#FCE4D6[/URL] , colspan: 3"]Year to date[/TD]
[TD="width: 104, bgcolor: transparent"][/TD]
[TD="width: 101, bgcolor: transparent"][/TD]
[TD="width: 108, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 85, bgcolor: transparent"][/TD]
[TD="width: 75, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2018[/TD]
[TD="bgcolor: transparent"]New Jersey[/TD]
[TD="bgcolor: transparent"]Pink[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]February[/TD]
[TD="bgcolor: transparent"]12[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Period 1[/TD]
[TD="bgcolor: transparent"]Period 2[/TD]
[TD="bgcolor: transparent"]Period 3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent"]February[/TD]
[TD="bgcolor: transparent"]March[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2018[/TD]
[TD="bgcolor: transparent"]New Jersey[/TD]
[TD="bgcolor: transparent"]Pink[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2018[/TD]
[TD="bgcolor: transparent"]New Jersey[/TD]
[TD="bgcolor: transparent"]Pink[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2019[/TD]
[TD="bgcolor: transparent"]Virginia[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2017[/TD]
[TD="bgcolor: transparent"]Virginia[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]9[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2018[/TD]
[TD="bgcolor: transparent"]Virginia[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2019[/TD]
[TD="bgcolor: transparent"]Virginia[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]6[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Cross posted https://chandoo.org/forum/threads/helping-creating-a-possible-sum-if-index-match-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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Help finding a value with two conditions, one of them being a date and the other being text
2
Replies
11
Views
1K
frankieung
F
T
  • Question Question
Sum-Unique - filter
Replies
8
Views
484
Peter_SSs
Peter_SSs
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
S
  • Question Question
#CALC! error when using INDIRECT (the cause?)
Replies
9
Views
655
Fluff
Fluff
W
  • Solved
Filter/Matching HELP
Replies
5
Views
451
Alex Blakenburg
A

Forum statistics

Threads
1,223,886
Messages
6,175,193
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top