summed match and index

M

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
Can anyone help me finish this off
I need a single formula that will give me a total figure each day.

I am trying to get a total weight for each days sales

Sheet 1 holds the unique ID and total items sold for each day
Sheet 2 holds the unique ID and the weight of each item.

I have got as far as creating helper cells but this is making the whole sheet way too big. I know there is a more efficient way



Heres my example for one day
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 76px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Sheet 1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Sheet 2</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">UID</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">sold</TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">UID</TD><TD style="FONT-WEIGHT: bold">Weight</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">347</TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">0.15</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">270</TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">3.9</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">90</TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">0.2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">120</TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">3.6</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">237</TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">0.25</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Total weight</TD><TD style="TEXT-ALIGN: right">2895.3</TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">2.5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">3.5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">3.5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>Helper cells</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">UID</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1353.3</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">67.5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">225</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">420</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">829.5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B8</TD><TD>=SUM(B13:B17)</TD></TR><TR><TD>B13</TD><TD>=INDEX(G3:G11,MATCH(A3,F3:F11,0))*B3</TD></TR><TR><TD>B14</TD><TD>=INDEX(G4:G12,MATCH(A4,F4:F12,0))*B4</TD></TR><TR><TD>B15</TD><TD>=INDEX(G5:G13,MATCH(A5,F5:F13,0))*B5</TD></TR><TR><TD>B16</TD><TD>=INDEX(G6:G14,MATCH(A6,F6:F14,0))*B6</TD></TR><TR><TD>B17</TD><TD>=INDEX(G7:G15,MATCH(A7,F7:F15,0))*B7</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Martin
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
HI
try C3= B3*(Vlookup(A3,Sheet2!$A$2:$B$7,2,false) copy the formula down to the last row
Ravi
 
Upvote 0
Hi ravishankar

That does what my match and index already does
Rich (BB code): 
=INDEX(G3:G11,MATCH(A3,F3:F11,0))*B3

I am trying to get rid of the copy and paste down bit.
My guess is it will involve an array {Ctrl,Shft,Enter}
but my attempts have so far been futile.

I am trying to get rid of A11:B17 in my example

Martin
 
Upvote 0
To all my friends who have tried to do this:

I have found the answer here:
Excel 2002 Formulas by the great JW page 202
Computing a Comulative Sum
Code: 
{=SUM(INDEX(G3:G11,MATCH($A3:A7,F3:F11,0))*B3)}
confirm with {ctrl shft enter}
How close was I

That beer will go down so much better tonight now :biggrin:
 
Upvote 0
Am I talking out of my bottom or what!

One too many coffees and an advent chocolate has made my brain soft

That hasn't actually done anything has it!! :ROFLMAO:
 
Upvote 0
Hello MartinL,

I'm not sure this is possible without a helper Column. I've played around with a few ideas without any joy, but perhaps one of the formula gurus on here might be able to offer a solution.

Any how, here's how I would do this with the use of a helper Column:

Excel Workbook
ABCDEFG
1UIDsoldWeightUIDWeight
223473.910.15
352700.2523.9
46902.530.2
571203.543.6
682373.550.25
7Total Weight2895.362.5
873.5
983.5
1092
Sheet2
Excel 2003
Cell Formulas
RangeFormula
C2=VLOOKUP(A2,F$2:G$10,2,0)
C3=VLOOKUP(A3,F$2:G$10,2,0)
C4=VLOOKUP(A4,F$2:G$10,2,0)
C5=VLOOKUP(A5,F$2:G$10,2,0)
C6=VLOOKUP(A6,F$2:G$10,2,0)
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
Matty
 
Upvote 0
Using Matty's setup - if the intention is to generage B7 without C then you can use:

=SUMPRODUCT(SUMIF($F$2:$F10,$A$2:$A$6,$G$2:$G$10),$B$2:$B$6)
 
Upvote 0
With the example shown above, try


try
=SUMPRODUCT(B2:B6,LOOKUP(A2:A6,F2:$G$9))

or equivalent

=SUMPRODUCT(B2:B6,LOOKUP(A2:A6,{1,0.15;2,3.9;3,0.2;4,3.6;5,0.25;6,2.5;7,3.5;8,3.5}))
 
Upvote 0
Nice solutions, guys! Have learnt something new today which will no doubt prove useful in future!

Cheers,

Matty
 
Upvote 0
Special Thanks to Matty for keeping this alive.
I went home so uptight on Friday
Just switched on laptop to try and unwind and saw this in my email box.

DonkeyOte thanks for your input,
Dave I have to bow down, your solution is so cool

And there was me using match and index and arrays and probably the most cackhanded ways of trying to do something imaginable.

Martin
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Picture won't paste in cell overlay
Replies
0
Views
108
Cerka
C
E
  • Question
Adding a custom message.
Replies
1
Views
480
Micron
M
S
  • Solved
Please help me with my workbook as Iam novice to Excel.
Replies
1
Views
286
kevin9999
K
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
419
Alex Blakenburg
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,216,377
Messages
6,130,265
Members
449,569
Latest member
sukming

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