Lookup Help??

  • Thread starter Thread starter Dluxe
  • Start date Start date
D

Dluxe

This is a great forum! Thanks for everyone who posts (both questions and
answers).

I'm a newbie to excel, but I know a little bit.

I'm faced with a worksheet that looks something like this

A B
--- ---
403 Raw Goods
102 Paper
52 Packaging
2 Other


The values in column A are the number of clients our company has for a
particular 'product area' (column B). The values in A are generated using a
formula like this for the Raw Goods tally:

=SUMPRODUCT((Purchases="Fibre Stock")+(Purchases="Polymer
Flake")+(Purchases="MatPres"))

All the raw data is stored on a separate sheet and so far it works great.
The trick is that a couple formulas have so many products and other
conditions that the formulas are getting to be too long. If we need to add
a new 'breakdown' to a particular area's tally, we're screwed.

I'm sure that the way to address this is through some sort of LOOKUP table
that lists countries and assigns them a code... like:
Fibre Stock RAW
Polymer Flake RAW
MatPres RAW
Boxes PKG
Tubs PKG
etc

And then use a SUMPRODUCT or COUNTIF with a nested LOOKUP function to take a
paricular client's main product purchases, look them up, find the code, and
tally them.

The problem is I can't see to get the syntax down. Can anyone give me a
hand or point me in the direction of a good lookup formula help?

Thanks, Dluxe
 
You could reduce the SUMPRODUCT formula like so

=SUMPRODUCT(--(Purchases={"Fibre Stock","Polymer Flake","MatPres"}))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
That's AWESOME!!! Thanks so much for the tip. Just out of curiosity,
though, was there a way to do it with lookups? I just think that's going to
have some utility down the line for me as well.

Thanks either way!

Brian
 
Back
Top