vlookup and sumproduct combo?

[rfinnegan]

Hi All:

I’m trying to perform a vlookup on a file in which the figures I’m looking for are set as text and have accomplished that with the help of this board [ =VLOOKUP(TEXT($A7,"0"),'data.xls'!$A$1:$CB$99,59,0) ].

Now I’m trying to show the sum of 4 rows of data in the data.xls sheet, all of which have to the same vlookup value.

For example – I’m performing a vlookup on a file named data.xls. The only identifier I can use in my vlookup is the store number (e.g. 14) which is in column A. In addition to the file being formatted as text, the store number appears from 5-8 times in column A, once for each product type. I need a formula that will find the store number, and then look in column G which has the product number (1-6), and then sum the figures in column L for products 3,4,5,6.

In other words what I’m trying to tell Excel is - Go to the data.xls file and look for the number 14 in column A (even though it’s formatted as text),
If column A=14, and column G=3, take the number in column L
If column A=14, and column G=4, take the number in column L
If column A=14, and column G=5, take the number in column L
If column A=14, and column G=6, take the number in column L
Now add all the column L figures from above.

I’m trying to do this in Excel 2003 .

Thanks in advance.

 

[scottylad2]

Is the cash to be summed also formatted as text?

 

[rfinnegan]

Yes. The entire sheet is formatted as text.

 

[scottylad2]

Excel Workbook
	G	H	I	J
13	Store	Product Number	Total
14	14	6	2910
15
16	Store Number	Product Number	Sales As Text	Converted to Numeric
17	3	3	291.00	291.00
18	18	5	1,186.00	1,186.00
19	20	6	933.00	933.00
20	20	2	1,086.00	1,086.00
21	8	5	1,187.00	1,187.00
22	6	2	1,275.00	1,275.00
23	10	2	511.00	511.00
24	7	1	391.00	391.00
25	15	6	652.00	652.00
26	11	1	455.00	455.00
27	6	2	1,101.00	1,101.00
28	20	6	1,270.00	1,270.00
29	6	6	782.00	782.00
30	14	6	1,078.00	1,078.00
31	14	6	1,104.00	1,104.00
32	20	5	704.00	704.00
33	1	3	856.00	856.00
34	5	3	501.00	501.00
35	10	2	688.00	688.00
36	14	6	728.00	728.00
37	19	5	971.00	971.00
38	16	1	1,181.00	1,181.00
39	11	5	1,215.00	1,215.00
40	3	5	845.00	845.00
Sheet1

maybe?

its not obvious from the sample, but all of the first three columns are text. the 4th has converted the cash amounts from text to numeric simply by adding a 0 onto the text column entries

 

[markmzz]

If I understand correctly what you want, then this can help you:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><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: #161120;text-align: center;">1</td><td style="text-align: center;;">Col01</td><td style="text-align: center;;">Col02</td><td style="text-align: center;;">Col03</td><td style="text-align: center;;">Col04</td><td style="text-align: center;;">Col05</td><td style="text-align: center;;">Col06</td><td style="text-align: center;;">Col07</td><td style="text-align: center;;">Col08</td><td style="text-align: center;;">Col09</td><td style="text-align: center;;">Col10</td><td style="text-align: center;;">Col11</td><td style="text-align: center;;">Col12</td><td style="text-align: right;;"></td><td style="text-align: center;;">Number</td><td style="text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">14</td><td style="text-align: center;;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">18</td><td style="text-align: right;;"></td><td style="text-align: center;;">14</td><td style="text-align: center;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">16</td><td style="text-align: center;;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">12</td><td style="text-align: center;;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">14</td><td style="text-align: center;;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">12</td><td style="text-align: center;;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">14</td><td style="text-align: center;;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">15</td><td style="text-align: center;;">15</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">12</td><td style="text-align: center;;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">14</td><td style="text-align: center;;">14</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">12</td><td style="text-align: center;;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">15</td><td style="text-align: center;;">15</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">12</td><td style="text-align: center;;">12</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">13</td><td style="text-align: center;;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">20</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:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">O2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">A2:A21=TEXT(<font color="Green">N2,"00"</font>)</font>)*(<font color="Red">G2:G21={3,4,5,6}</font>)*(<font color="Red">L2:L21</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmz

 

[scottylad2]

If you don#t have space or inclination to create an extra row for converting text then this will convert within the sumproduct

SUMPRODUCT(--(G17:G40=G14),--(H17:H40=H14),I17:I40+0)

 

[markmzz]

Rfinnegan,

I didn't see this "The entire sheet is formatted as text. "

New formula:

=SUMPRODUCT((1*A2:A21=N2)*(1*G2:G21={3,4,5,6})*(L2:L21))

Markmzz

 

[rfinnegan]

Thanks to you both. I finally got it to work using markmzz first solution. I say finally, because I managed to dork-up a character or two as I translated the solution to my sheet.
Scottylads solution probably works too...I'm sure I dorked it up, as I kept ketting a "0".

Again, a million thanks. I owe you both a beer.

 

[markmzz]

I finally got it to work using markmzz first solution.

Rfinnegan,

Thanks for the feedback.

Markmzz

 

[scottylad2]

My solution was based entirely on text, including the lookup values. Only required the cash totals to be converted from text to numeric in the final part of the argument the +0 part. I guess your looking up numbers rather than text