sum of a named range with values based on vlookup results

[Solutions Manager]

I have been poking around and can't seem to find what I am looking for so
here goes. In Excel 2003, I have a spreadsheet with two columns in
particular. For this example, Column A is called Size. The values entered are
alphanumeric ad codes. Column B is called PageCount. The values are the
numerical result of a VLOOKUP of the Size code. (I have a table that lists
all the adcodes and resulting info) Below is example of the codes and their
numerical pagecount values:

Size PageCount
4-s .25
2-hf .50
1-bc 1.0
crawl 0

The question is as follows. Right now I add the PageCount column to sum
sizes. Is there a way to use a single formula to SUM the Size column with the
Values being those in the PageCount column? I am trying to eliminate the
PageCount column alltogether because the results always end up in one cell
anyway. Hopefully this isn't poorly worded.

 

[barry houdini]

I have been poking around and can't seem to find what I am looking for so
here goes. In Excel 2003, I have a spreadsheet with two columns in
particular. For this example, Column A is called Size. The values enteredare
alphanumeric ad codes. Column B is called PageCount. The values are the
numerical result of a VLOOKUP of the Size code. (I have a table that lists
all the adcodes and resulting info) Below is example of the codes and their
numerical pagecount values:

Size   PageCount
4-s      .25
2-hf     .50
1-bc    1.0
crawl     0

The question is as follows. Right now I add the PageCount column to sum
sizes. Is there a way to use a single formula to SUM the Size column withthe
Values being those in the PageCount column? I am trying to eliminate the
PageCount column alltogether because the results always end up in one cell
anyway. Hopefully this isn't poorly worded.

As long as your adcodes appear only once each in your VLOOKUP table
the you could use a SUMIF like

=SUMPRODUCT(SUMIF(adcodes,B2:B5,sizecodes))

 

[Solutions Manager]

thank you for the suggestion, but I have to try to be clear. Column B exists
now. I want Column B to disappear and to be able to do the math on Column A
through a vlookup of the values.

 

[T. Valko]

Let's see if I understand this...

Based on your posted sample, you want a single cell formula that returns
1.75?

Where's your lookup table?

Try this replacing the references with your own:

=SUMPRODUCT(--(ISNUMBER(MATCH(lookup_table_column_1,SIZE,0))),lookup_table_column_with_Page_Count_values)

 

[Shane Devenshire]

Hi,

Let's suppose that the formula in column B reads =VLOOKUP(A1,Table,2,FALSE)
(you didn't show us the formula)

You can get the whole result by using the following Array formula:

=SUM(LOOKUP(Size,Table))

Press Shift+Ctrl+Enter to enter this formula. Or if you don't want to use
the array form then

=SUMPRODUCT(LOOKUP(Size,Table))

If the lookup values are not in the last column of the table range then use

=SUM(LOOKUP(Size,A1:A10,B1:B10))

Where A1:A10 is the lookup column of the Table and B1:B10 is the column
containing the values you want to return. This is and array function.

 

[T. Valko]

Note that if you use the LOOKUP function your lookup_table *must* be sorted
in ascending order based on "size".

 

[barry houdini]

thank you for the suggestion, but I have to try to be clear. Column B exists
now. I want Column B to disappear and to be able to do the math on ColumnA
through a vlookup of the values.

Sorry, I realised that but used the wrong cell references, I meant

=SUMPRODUCT(SUMIF(adcodes,A2:A5,sizecodes))

Where A2:A5 contains

4-s
2-hf
1-bc
crawl

 

[Solutions Manager]

Here is the specifics.
The worksheet is named "sales" and I have another worksheet named "storebox"
with a table named "tbl.specs". The table contains adcodes sorted in
ascending order with various columns of data about each such as the page
count value.

In the sales spreadsheet the ad sizes are in column C. I have my current
page count VLOOKUPS are in column "AA" (named sb.pages). Here is the formula
for a typical row:
=IF(ISBLANK(INDIRECT("c"&ROW())),"",VLOOKUP(INDIRECT("c"&ROW()),tbl.specs,2,0))

I have another sheet named MSR with a list of stats, among which is the page
count. That formula is:
=SUM(sb.pages).

So does this help? I hope to eliminate the sb.pages column (AA) and just
have the formula in the MSR sheet give me the page count by looking at the
values in the size column and summing the pagecount of each individual cell
in the named range "size".

Thank you for all of your help. I assume I cannot post the actual workbook?

 

[Solutions Manager]

Sorry i must not have been clear. In my example I showed a column B, but in
real life I want to eliminate column B. I need a formula that does a VLOOKUP

 

[Solutions Manager]

I will try this. Thanks for the suggestion.
Again in my example, the size codes are in column C and I have a table named
"tbl.specs" with the adcodes as the first column in ascending order. Column 2
contains the page count for each ad.

Thank you again.

 

[T. Valko]

Ok, the formula I suggested will work but we'll have to tweak it for your
named range "tbl.specs".

=SUMPRODUCT(--(ISNUMBER(MATCH(INDEX(tbl.specs,,1),Cn:Cn,0))),INDEX(tbl.specs,,2))

Replace Cn:Cn with the actual range references for your "adcodes".

 

[Solutions Manager]

This comes up with a completely different result from the current formula
that sums the page count column.
I might have misunderstood something. The column which contains the sizes is
really a list of codes for various ads sold. The same code can appear
multiple times. I think your formula only ads each code once.

So this should equal 3.5:
4-s
2-hf
1-bc
crawl
4-s
2-hf
1-bc
crawl

But your formula stays at 1.75

 

[T. Valko]

The same code can appear multiple times.

Knowing all the details can make a big difference!

Try this:

=SUMPRODUCT(COUNTIF(Cn:Cn,INDEX(tbl.specs,,1)),INDEX(tbl.specs,,2))

 

[Solutions Manager]

This is PERFECT. Thank you for your time and patience. My brain filled in too
many missing details. I need to explain more clearly. Thank you again.

 

[Solutions Manager]

Using this same example. I have another long shot question. Column D of this
sheet is ad colors. Again, Right now I use the sb.pages to determine pages of
a color sold.
Here is a formula: =SUMIF(color,"=green",sb.pages)

In the example below, the color green = .5 because each 4-s equals a page
count of .25
Since sb.pages is non-existent due to the other awesome formula, can I again
hook into the size column with to determine the pagecount of the green ads?

size color
4-s green
4-s cmyk
4-s black
4-s green

 

[T. Valko]

You're welcome. Thanks for the feedback!

It was just a matter of time before we got this sorted out!

 

[T. Valko]

Is "green" part of a lookup_table?

If it is then something like this:

=SUMPRODUCT(--(Cn:Cn="4-s"),--(Dnn="green"))*VLOOKUP("green",table,column_number,0)

 

[Solutions Manager]

No green is really just one possible color of any given ad. Ads can be
various colors, but the colors themselves have no value. This is another
reason I originally created the sb.pages column to lookup the page count for
an adsize. Then for example, I simply used the sumif to say if the color is
"green" add the sb.pages column.

I guess I really rely on my column of sb.pages as an easy way to get the
page count out into the spreadsheet in an easy way. Thanks for everything.
This may not be possible though...

 

[T. Valko]

Let's not give up!

Is this what you want....

size color
4-s green
4-s cmyk
4-s black
4-s green

size value
1-s 3
2-s 5
3-s 1
4-s 2

For every color green lookup the size value and sum those all together?
Based on the above sample the result would be 4.

 

[Alibo]

From the answers you have given here I think you will e able to help me...

I am trying to get the sum of multiple returns on a VLOOKUP. As an example:

Jan 24
Jan 57
Jan 239
Feb 72
Mar 16
Mar 44

I want to get a result that looks like:

Jan Feb Mar
320 72 60

I have tried:

=SUMPRODUCT(VLOOKUP((Jan,A2:B7,2,FALSE))

But unfortunatley that just returns 24.

Can you help?

Alibo