Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What formula!?!?!
Hi to All, First of all sorry for my english... I need help with a formula in excel. I have in one sheet several columns with data. Imagine that in column A I have a code and in column F I have a value. I need in sheet number 2 the total of values of the codes that finish in some values. Example: Sheet 1 Code ---- Value AB123 -----2 AC123 -----3 AB133 -----6 AA124 -----1 AB124 -----8 Sheet 2 Semi-code-----Total 123-------------5 133-------------6 124-------------9 This semicode can be hard coded or the right 3 digits of Code. Thank you very much JK -- jamkul ------------------------------------------------------------------------ jamkul's Profile: http://www.excelforum.com/member.php...o&userid=36561 View this thread: http://www.excelforum.com/showthread...hreadid=563105 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What formula!?!?!
Place this in Column B of sheet 2:
=SUMPRODUCT((RIGHT(Sheet1!A1:A6,3)=TEXT(Sheet2!A1, "000"))*(Sheet1!B1:B6)) You must use defined ranges, A:A and B:B do not work with SumProduct. HTH Die_Another_Day jamkul wrote: Hi to All, First of all sorry for my english... I need help with a formula in excel. I have in one sheet several columns with data. Imagine that in column A I have a code and in column F I have a value. I need in sheet number 2 the total of values of the codes that finish in some values. Example: Sheet 1 Code ---- Value AB123 -----2 AC123 -----3 AB133 -----6 AA124 -----1 AB124 -----8 Sheet 2 Semi-code-----Total 123-------------5 133-------------6 124-------------9 This semicode can be hard coded or the right 3 digits of Code. Thank you very much JK -- jamkul ------------------------------------------------------------------------ jamkul's Profile: http://www.excelforum.com/member.php...o&userid=36561 View this thread: http://www.excelforum.com/showthread...hreadid=563105 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What formula!?!?!
Thanks for your reply, but it didn't work. Maybe I did some confusion... In sheet 1 I have something like 30000 codes and values. In sheet 2 I have some subtotals of the codes but these subtotals were calculated in other place (an aplication). Now I need to verify that the results are correct. So I need to sum the values of sheet 1 if the right(code,3) is equal to the sheet 2 code. I need to have this ready in 1 hour... can anybody please help me?? Thanks JK -- jamkul ------------------------------------------------------------------------ jamkul's Profile: http://www.excelforum.com/member.php...o&userid=36561 View this thread: http://www.excelforum.com/showthread...hreadid=563105 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |