Index Match, sum if or vlookup?

P

Pripri

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


Year to date
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]April[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]January[/TD]
[TD="bgcolor: transparent"]February[/TD]
[TD="bgcolor: transparent"]March[/TD]
[TD="bgcolor: transparent"]April[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Florida[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]California[/TD]
[TD="bgcolor: transparent"]Green[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]New York[/TD]
[TD="bgcolor: transparent"]Yellow[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
According to your example, only February values ​​should be added.

Then the values ​​are added in green:

<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:44.67px;" /><col style="width:131.17px;" /><col style="width:46.57px;" /><col style="width:52.28px;" /><col style="width:52.28px;" /><col style="width:52.28px;" /><col style="width:19.01px;" /><col style="width:51.33px;" /><col style="width:58.93px;" /><col style="width:42.77px;" /></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></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Year</td><td >Year to date</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 style="text-align:right; ">2018</td><td >New Jersey</td><td >Pink</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 > </td><td style="background-color:#92d050; ">February</td><td style="text-align:right; ">12</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></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td >Period1</td><td >Period2</td><td >Period3</td><td > </td><td style="background-color:#ffff00; text-align:center; ">January</td><td style="background-color:#ffff00; text-align:center; ">February</td><td style="background-color:#ffff00; text-align:center; ">March</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">2018</td><td >New Jersey</td><td >Pink</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td > </td><td style="text-align:right; ">5</td><td style="background-color:#92d050; text-align:right; ">10</td><td style="text-align:right; ">15</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">2018</td><td >New Jersey</td><td >Pink</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td > </td><td style="text-align:right; ">1</td><td style="background-color:#92d050; text-align:right; ">2</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">2019</td><td >Virginia</td><td >Yellow</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td > </td><td style="text-align:right; ">10</td><td style="text-align:right; ">15</td><td style="text-align:right; ">20</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">2017</td><td >Virginia</td><td >Blue</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td style="text-align:right; ">6</td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">7</td><td style="text-align:right; ">9</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">2018</td><td >Virginia</td><td >Yellow</td><td style="text-align:right; ">5</td><td style="text-align:right; ">6</td><td style="text-align:right; ">7</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">2019</td><td >Virginia</td><td >Yellow</td><td style="text-align:right; ">6</td><td style="text-align:right; ">7</td><td style="text-align:right; ">8</td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">4</td><td style="text-align:right; ">6</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 >C3</td><td >=SUMPRODUCT((A6:A11=A2)*(B6:B11=B2)*(C6:C11=C2)*(H5:S5=B3)*(H6:S11))</td></tr></table></td></tr></table>
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
You must log in or register to reply here.

Similar threads

D
  • Question Question
Clinching NHL Playoff Spot
Replies
2
Views
74
dotcanada
D
F
  • Solved
Help finding a value with two conditions, one of them being a date and the other being text
2
Replies
11
Views
1K
frankieung
F
T
  • Question Question
Sum-Unique - filter
Replies
8
Views
489
Peter_SSs
Peter_SSs
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
S
  • Question Question
#CALC! error when using INDIRECT (the cause?)
Replies
9
Views
664
Fluff
Fluff

Forum statistics

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

Share this page

We've detected that you are using an adblocker.

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

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

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

Disable uBlock

Follow these easy steps to disable uBlock

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