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.

On Jan 1, 8:50*pm, Solutions Manager
wrote:
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.

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))

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.

"barry houdini" wrote:

On Jan 1, 8:50 pm, Solutions Manager
wrote:
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.

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))

On Jan 1, 9:13*pm, Solutions Manager
wrote:
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.

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

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))),lo okup_table_column_with_Page_Count_values)

--
Biff
Microsoft Excel MVP


"Solutions Manager" wrote in
message ...
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.

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(INDIRE CT("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?

"T. Valko" wrote:

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))),lo okup_table_column_with_Page_Count_values)

--
Biff
Microsoft Excel MVP


"Solutions Manager" wrote in
message ...
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.

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" wrote:

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))),lo okup_table_column_with_Page_Count_values)

--
Biff
Microsoft Excel MVP


"Solutions Manager" wrote in
message ...
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.

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))),IND EX(tbl.specs,,2))

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


--
Biff
Microsoft Excel MVP


"Solutions Manager" wrote in
message ...
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" wrote:

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))),lo okup_table_column_with_Page_Count_values)

--
Biff
Microsoft Excel MVP


"Solutions Manager" wrote in
message ...
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.

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" wrote:

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))),IND EX(tbl.specs,,2))

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


--
Biff
Microsoft Excel MVP


"Solutions Manager" wrote in
message ...
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" wrote:

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))),lo okup_table_column_with_Page_Count_values)

--
Biff
Microsoft Excel MVP


"Solutions Manager" wrote in
message ...
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.

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)),IND EX(tbl.specs,,2))

--
Biff
Microsoft Excel MVP


"Solutions Manager" wrote in
message ...
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" wrote:

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))),IND EX(tbl.specs,,2))

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


--
Biff
Microsoft Excel MVP


"Solutions Manager" wrote in
message ...
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" wrote:

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))),lo okup_table_column_with_Page_Count_values)

--
Biff
Microsoft Excel MVP


"Solutions Manager" wrote
in
message ...
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.

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.



--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Solutions Manager" wrote:

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.

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

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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.



--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Solutions Manager" wrote:

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.

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
on each value in COLUMN A and then SUMS the pagecount value for each adcode,
all in one formula.


"Shane Devenshire" wrote:

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.



--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Solutions Manager" wrote:

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.