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) |
All times are GMT +1. The time now is 09:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com