Index Match, sum if or vlookup?

Pripri · Aug 20, 2019

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

Eric W · Aug 20, 2019

Try:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="background-color: #FAFAFA;;">Year to date</td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="background-color: #FAFAFA;;">Blue</td><td style="background-color: #FAFAFA;;">Florida</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="background-color: #FAFAFA;;">April</td><td style="text-align: right;background-color: #FAFAFA;;">20</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="background-color: #FAFAFA;;">January</td><td style="background-color: #FAFAFA;;">February</td><td style="background-color: #FAFAFA;;">March</td><td style="background-color: #FAFAFA;;">April</td><td style="color: #333333;background-color: #FAFAFA;;">May</td><td style="background-color: #FAFAFA;;">June</td><td style="background-color: #FAFAFA;;">July</td><td style="background-color: #FAFAFA;;">August</td><td style="background-color: #FAFAFA;;">September</td><td style="background-color: #FAFAFA;;">October</td><td style="background-color: #FAFAFA;;">November</td><td style="background-color: #FAFAFA;;">December</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="background-color: #FAFAFA;;">Florida</td><td style="background-color: #FAFAFA;;">Blue</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="background-color: #FAFAFA;;">Florida</td><td style="background-color: #FAFAFA;;">Yellow</td><td style="text-align: right;background-color: #FAFAFA;;">10</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">10</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="background-color: #FAFAFA;;">California</td><td style="background-color: #FAFAFA;;">Green</td><td style="text-align: right;background-color: #FAFAFA;;">10</td><td style="text-align: right;background-color: #FAFAFA;;">10</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="background-color: #FAFAFA;;">New York</td><td style="background-color: #FAFAFA;;">Yellow</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A4</th><td style="text-align:left">=LOOKUP(<font color="Blue">9+E307,C10:N10,C9:N9</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=SUM(<font color="Blue">INDEX(<font color="Red">C10:N13,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">A10:A13</font>)-ROW(<font color="Teal">A10</font>)+1</font>)/(<font color="Purple">(<font color="Teal">A10:A13=B3</font>)*(<font color="Teal">B10:B13=A3</font>)</font>),1</font>),0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

gaz_chops · Aug 20, 2019

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]

DanteAmor · Aug 20, 2019

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

Pripri · Aug 20, 2019

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

Eric W · Aug 20, 2019

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?

Pripri · Aug 20, 2019

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

Eric W · Aug 20, 2019

Try this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="background-color: #FAFAFA;;">Year to date</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">2018</td><td style="background-color: #FAFAFA;;">Blue</td><td style="background-color: #FAFAFA;;">Florida</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="background-color: #FAFAFA;;">April</td><td style="text-align: right;background-color: #FAFAFA;;">20</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="background-color: #FAFAFA;;">January</td><td style="background-color: #FAFAFA;;">February</td><td style="background-color: #FAFAFA;;">March</td><td style="background-color: #FAFAFA;;">April</td><td style="color: #333333;background-color: #FAFAFA;;">May</td><td style="background-color: #FAFAFA;;">June</td><td style="background-color: #FAFAFA;;">July</td><td style="background-color: #FAFAFA;;">August</td><td style="background-color: #FAFAFA;;">September</td><td style="background-color: #FAFAFA;;">October</td><td style="background-color: #FAFAFA;;">November</td><td style="background-color: #FAFAFA;;">December</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;background-color: #FAFAFA;;">2018</td><td style="background-color: #FAFAFA;;">Florida</td><td style="background-color: #FAFAFA;;">Blue</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;background-color: #FAFAFA;;">2019</td><td style="background-color: #FAFAFA;;">Florida</td><td style="background-color: #FAFAFA;;">Yellow</td><td style="text-align: right;background-color: #FAFAFA;;">10</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">10</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;background-color: #FAFAFA;;">2017</td><td style="background-color: #FAFAFA;;">Florida</td><td style="background-color: #FAFAFA;;">Blue</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;background-color: #FAFAFA;;">2018</td><td style="background-color: #FAFAFA;;">Florida</td><td style="background-color: #FAFAFA;;">Yellow</td><td style="text-align: right;background-color: #FAFAFA;;">1</td><td style="text-align: right;background-color: #FAFAFA;;">1</td><td style="text-align: right;background-color: #FAFAFA;;">1</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;background-color: #FAFAFA;;">2017</td><td style="background-color: #FAFAFA;;">New York</td><td style="background-color: #FAFAFA;;">Blue</td><td style="text-align: right;background-color: #FAFAFA;;">3</td><td style="text-align: right;background-color: #FAFAFA;;">1</td><td style="text-align: right;background-color: #FAFAFA;;">1</td><td style="text-align: right;background-color: #FAFAFA;;">1</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;background-color: #FAFAFA;;">2019</td><td style="background-color: #FAFAFA;;">California</td><td style="background-color: #FAFAFA;;">Green</td><td style="text-align: right;background-color: #FAFAFA;;">10</td><td style="text-align: right;background-color: #FAFAFA;;">10</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;background-color: #FAFAFA;;">5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;background-color: #FAFAFA;;">2018</td><td style="background-color: #FAFAFA;;">New York</td><td style="background-color: #FAFAFA;;">Yellow</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;background-color: #FAFAFA;;">2</td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;background-color: #FAFAFA;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=LOOKUP(<font color="Blue">9+E307,D10:O10,D9:O9</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C4</th><td style="text-align:left">=SUM(<font color="Blue">INDEX(<font color="Red">D10:O150,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">B10:B150</font>)-ROW(<font color="Teal">B10</font>)+1</font>)/(<font color="Purple">(<font color="Teal">A10:A150=A3</font>)*(<font color="Teal">B10:B150=C3</font>)*(<font color="Teal">C10:C150=B3</font>)</font>),1</font>),0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Pripri · Aug 22, 2019

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

Fluff · Aug 22, 2019

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.

Index Match, sum if or vlookup?

Pripri

New Member

Eric W

MrExcel MVP

gaz_chops

Well-known Member

DanteAmor

Well-known Member

Pripri

New Member

Eric W

MrExcel MVP

Pripri

New Member

Eric W

MrExcel MVP

Pripri

New Member

Fluff

MrExcel MVP, Moderator

Forum statistics

Share this page

Some videos you may like

This Week's Hot Topics