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

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.

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

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.

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.

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.

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.

"T. Valko" wrote:

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.

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

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.

You're welcome. Thanks for the feedback!

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

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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.

Is "green" part of a lookup_table?

If it is then something like this:

=SUMPRODUCT(--(Cn:Cn="4-s"),--(Dn:Dn="green"))*VLOOKUP("green",table,column_numb er,0)



--
Biff
Microsoft Excel MVP


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

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.

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

Is "green" part of a lookup_table?

If it is then something like this:

=SUMPRODUCT(--(Cn:Cn="4-s"),--(Dn:Dn="green"))*VLOOKUP("green",table,column_numb er,0)



--
Biff
Microsoft Excel MVP


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

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.

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.


--
Biff
Microsoft Excel MVP


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

Is "green" part of a lookup_table?

If it is then something like this:

=SUMPRODUCT(--(Cn:Cn="4-s"),--(Dn:Dn="green"))*VLOOKUP("green",table,column_numb er,0)



--
Biff
Microsoft Excel MVP


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

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.

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

see your other post

--
Biff
Microsoft Excel MVP


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