Index Match, sum if or vlookup?

P

Pripri

New Member
Joined
Aug 18, 2019
Messages
13

Looking for the following resuts:


Year to date
BlueFlorida
April20
JanuaryFebruaryMarchApril
FloridaBlue5555
FloridaYellow105510
CaliforniaGreen101055
New YorkYellow2222
<colgroup><col width="64" style="width: 48pt;" span="7"> <tbody> </tbody>
 
Eric W

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,811
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 />
 
G

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
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:
DanteAmor

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,922
Office Version
2007
Platform
Windows
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
 
P

Pripri

New Member
Joined
Aug 18, 2019
Messages
13
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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,811
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?
 
P

Pripri

New Member
Joined
Aug 18, 2019
Messages
13
Year to date
2018FloridaYellow
February15
JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctober
2018FloridaBlue5555
2019FloridaYellow105510
2017FloridaBlue5222
2018FloridaYellow1112
2017New YorkBlue3111
2019CaliforniaGreen101055
2018New YorkYellow2222
<colgroup><col width="64" style="width: 48pt;" span="13"> <tbody> </tbody>
 
Eric W

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,811
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 />
 
P

Pripri

New Member
Joined
Aug 18, 2019
Messages
13
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
2018New JerseyPink
February12
Period 1Period 2Period 3JanuaryFebruaryMarch
2018New JerseyPink55551015
2018New JerseyPink111123
2019VirginiaYellow1055101520
2017VirginiaBlue522579
2018VirginiaYellow111123
2019VirginiaYellow222246
<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> </tbody>
 
Fluff

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,545
Office Version
365
Platform
Windows
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.
 
You must log in or register to reply here.

Forum statistics

Threads
1,078,462
Messages
5,340,454
Members
399,376
Latest member
Tresfjording

Share this page

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    • Replies: 44
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    • Replies: 35
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    • Replies: 28
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    • Replies: 24
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    • Replies: 23
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    • Replies: 23
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top