Index Match, sum if or vlookup?

P

Pripri

New Member
Joined
Aug 18, 2019
Messages
24

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>
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
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>
 
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:
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>
 
Upvote 0
Cross posted https://chandoo.org/forum/threads/helping-creating-a-possible-sum-if-index-match-vlookup.42405/

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,214,405
Messages
6,119,315
Members
448,886
Latest member
GBCTeacher

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