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.