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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
1K
frankieung
F
T
  • Question Question
Sum-Unique - filter
Replies
8
Views
484
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
655
Fluff
Fluff
W
  • Solved
Filter/Matching HELP
Replies
5
Views
451
Alex Blakenburg
A

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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