SUMPROD and OTHER FUNCTIONS!!! VERY URGENT

[PaulinaDi]

Expecting to have better luck! Here I go again. Thanks in advanced

Hi:

I have a document that I need to constantly update as I receive new data for
it (as you may see in column named "Rev").

Rev Item Bulk Descr Price
1 1001 1 Tube 2.00
1 1001 1 Tube 2.00
1 1001 2 Tube 3.00
1 1002 6 Valve 5.00
2 1001 1 Tube 4.00
2 1002 6 Valve 8.00
2 1002 8 Valve 6.00
3 1001 1 Tube 4.00
3 1002 8 Valve 6.00
3 1001 2 Tube 4.00
3 1001 2 Tube 4.00
3 1002 8 Valve 10.00
4 1001 2 Tube 6.00
4 1001 1 Tube 5.00
4 1001 1 Tube 5.00
4 1001 2 Tube 6.00
4 1001 2 Tube 6.00
4 1002 6 Valve 10.00

Is there any way to get the price for another sheet using the SUMPRODUCT and
at the same time or just in another step compare if, this spreadsheet
contains this itemcode and bulk size just consider the last version on qty
and price and eliminate all the info before it? and if not, just leave the
info from the latest Revision version?

You can see it an eg in item 1002: the bulk size 8 "valve" version 3
desappears in version 4, but bulk size 6 valve version 2, dissapears in
version 3 but appears again in version 4.

The formula I'm using to get the Price from the other sheet is:
=SUMPRODUCT(--(AccCad!$E$15:$E$1415='TIE-INS-MAD'!N12),--(AccCad!$F$15:$F$1415='TIE-INS-MAD'!I12),AccCad!$AJ$15:$AJ$1415)/SUMPRODUCT(--(AccCad!$E$15:$E$1415='TIE-INS-MAD'!N12),--(AccCad!$F$15:$F$1415='TIE-INS-MAD'!I12))

BY THE WAY... how may I be able to reduce the formula above as I have to get
the info and then divide it from the values I get in order to get just the
price for one piece? (E contains Item Code, F contains de Bulk Size and AJ
contains the Price)

 

[PaulinaDi]

Hi SMartin:

Maybe I could not explain myself as I would like to.
I follow your instructions and I got some results.
By the way, it was a mistake about the valves of from rev 3. The correct
price was 6. I'm very sorry.
My table is just a little example of the big one. I'm really working with
4000 rows and about 15 or 16 columns.

In some cases I have the article form the revision 1, but in some cases the
article comes from a new revision or from the latest one. I have to review
always the database and update the price from another database to have the
final price on a Central Data Base. That's why I have to use the SUMPRODUCT
funcion to take the Prices from other spreadsheets, but as I have new
versions it is too crazy to know if the price is going up and up and up again
or going lower that before of just staying on the same level. Basically I
need to know how the price is moving and to take the latest price no matter
if it is the version 1, 2, 3, 4, etc. but I need to know which is the version
to have a reference.

On the other hand.... could you explain me the formulas you used before? I
only understood the first one that decrease the number of items for each new
version. But I got lost using the others and I didn´t know if I had to use
them in each row or just on the first line.

THANKS IN ADVANCED! You are a genious!!!!!!!!