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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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

D
  • Question Question
Clinching NHL Playoff Spot
Replies
2
Views
74
dotcanada
D
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
489
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
664
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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