Lookup

  • Thread starter Thread starter Matt C
  • Start date Start date
M

Matt C

Hi,

I'm looking to use the lookup functions in order to group some figures
together by product code. It's probably best if I detail what I'm trying to
do because the help menu doesn't seem to cater for sum totals.

I have two worksheets within an excel book:

Master - This sheet holds data on stock values, for each product code there
can be a number of rows
Comparison - This is going to be used to compare our stock system values to
our actual count values (hence need one row per code).

Master Sheet Layout:

Col A - Code
Col B - Product
Col C - Qty

Comparison Sheet Layout:

Col A - Code
Col B - Product
Col C - Qty

I was thinking about using a pivot table but you cannot quite manipulate the
data within the produced table so there isn't really an option. What I want
is for the comparison sheet in column C to hold the sum figure from the
multiple rows in the master sheet (from column C).

Can this be done from lookups or is there another way which I can look at
from help menu?


Cheers,

Matt
 
Don Guillett said:
I don't see any numbers displayed but a look in help for SUMIF may help.
Click to expand...

<snip>

Thanks for your response.

The SUMIF option doesn't help because I need to work from codes and not just
one column of data.

For example:

A01010 Jar A 50000
A01010 Jar A 50000

That's my main master count sheet, and I want a seperate worksheet to give
out a figure of:

A01010 Jar A 100000

Thus grouping all the seperate rows together into one row on the seperate
sheet.


Matt
 
You didn't say so!!
try
=sumproduct((a2:a200="A01010")*(b2:b200="Jar A")*c2:c200)
 
Matt,

You could try SUMPRODUCT. Say your master is A1:C100. In the
comparison, I am assuming that the Code and Product fields will be
populated to use for the lookup. In the first comparison Qty cell (for
example C2) try,

=SUMPRODUCT((Master!A1:A100=A2)*(Master!B1:B100=B2)*(Master!C1:C100))


This will match the Code and Product values in the comparison to the
master and return the sum of the Quantity from the Master sheet.

Does that help?

Steve
 
SteveG said:
Matt,

You could try SUMPRODUCT. Say your master is A1:C100. In the
comparison, I am assuming that the Code and Product fields will be
populated to use for the lookup. In the first comparison Qty cell (for
example C2) try,

=SUMPRODUCT((Master!A1:A100=A2)*(Master!B1:B100=B2)*(Master!C1:C100))


This will match the Code and Product values in the comparison to the
master and return the sum of the Quantity from the Master sheet.

Does that help?

Steve
Click to expand...

Hi Steve & Don,

Both of your replies were very helpful and I have found the solution!

=SUMPRODUCT(('Master Sheet'!A4:A511=A4)*('Master Sheet'!D4:D511))

Cheers for your help,

Matt
 
Matt C wrote...
....
Both of your replies were very helpful and I have found the solution!

=SUMPRODUCT(('Master Sheet'!A4:A511=A4)*('Master Sheet'!D4:D511))
Click to expand...

If this really does give the correct answers, you could have used (and
still should use)

=SUMIF('Master Sheet'!A4:A511,A4,'Master Sheet'!D4:D511)

SUMIF takes an optional 3rd argument. You can sum a different column
than the one to which you're applying a condition.
 
Back
Top