Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPROD and trying SUBSTITUTE, ELIMINATE or TAKE the last VE
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? 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE.... max. limit 8 :( | Excel Discussion (Misc queries) | |||
SUMPROD question | Excel Discussion (Misc queries) | |||
Apply Sumprod on mix text & numbers arrays | Excel Discussion (Misc queries) | |||
SUBSTITUTE | Excel Worksheet Functions | |||
substitute | Excel Worksheet Functions |