| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a short cut for this...
I have three ranges in different sheets, for a selected luk up CODE 1003 I
get total qty 1600 by =SUMIF($C$5:$C$10,$C3,D$5:D$10)+SUMIF(GURU!$C$3:$C $8,SAM!$C3,GURU!D$3:D$8)+SUMIF(JOYCE!$C$3:$C$8,SAM !$C3,JOYCE!D$3:D$8) Amount 4600 by SUMIF($C$5:$C$10,$C3,F$5:F$10)+SUMIF(GURU!$C$3:$C$ 8,SAM!$C3,GURU!F$3:F$8)+SUMIF(JOYCE!$C$3:$C$8,SAM! $C3,JOYCE!F$3:F$8) is there any better way, than repeating 3 times sumif. Here Sam, Guru & Joyce are sheet names (may be if can name the ranges, is it possible to locate qty column & total for specific query code, likewise for amount also.) In real data will be of 300 to 500 rows in each sheet. QUERY CODE QTY AMOUNT 1003 1600 4600 SAM CODE QTY PRICE AMOUNT 1001 200 2 400 1004 300 2 600 1003 400 2 800 1004 500 2 1000 1003 600 2 1200 GURU CODE QTY PRICE AMOUNT 1001 200 2 400 1002 200 2 400 1003 200 3 600 1002 200 2 400 1003 200 5 1000 JOYCE CODE QTY PRICE AMOUNT 1005 200 2 400 1004 200 2 400 1003 200 5 1000 1004 200 2 400 1005 200 2 400 thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need a short cut for this...
On Wed, 15 Feb 2006 05:28:29 -0800, Eddy Stan
wrote: I have three ranges in different sheets, for a selected luk up CODE 1003 I get total qty 1600 by =SUMIF($C$5:$C$10,$C3,D$5:D$10)+SUMIF(GURU!$C$3:$ C$8,SAM!$C3,GURU!D$3:D$8)+SUMIF(JOYCE!$C$3:$C$8,SA M!$C3,JOYCE!D$3:D$8) Amount 4600 by SUMIF($C$5:$C$10,$C3,F$5:F$10)+SUMIF(GURU!$C$3:$C $8,SAM!$C3,GURU!F$3:F$8)+SUMIF(JOYCE!$C$3:$C$8,SAM !$C3,JOYCE!F$3:F$8) is there any better way, than repeating 3 times sumif. Here Sam, Guru & Joyce are sheet names (may be if can name the ranges, is it possible to locate qty column & total for specific query code, likewise for amount also.) In real data will be of 300 to 500 rows in each sheet. QUERY CODE QTY AMOUNT 1003 1600 4600 SAM CODE QTY PRICE AMOUNT 1001 200 2 400 1004 300 2 600 1003 400 2 800 1004 500 2 1000 1003 600 2 1200 GURU CODE QTY PRICE AMOUNT 1001 200 2 400 1002 200 2 400 1003 200 3 600 1002 200 2 400 1003 200 5 1000 JOYCE CODE QTY PRICE AMOUNT 1005 200 2 400 1004 200 2 400 1003 200 5 1000 1004 200 2 400 1005 200 2 400 thanks in advance. Unfortunately, SUMIF is not a function that is usable with a 3D reference. However, Longre's free morefunc.xll add-in is available at http://xcell05.free.fr You can then use the THREED function to create an array from a 3D reference, and then use this formula: Assumes that your data table is in A1:D20 on each sheet. Joyce and Guru are the first and last sheets in the range. CODE being looked up is in A2 on the Active Sheet. Quantity =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THRE ED(Joyce:Guru!B2:B20)) Amount =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THRE ED(Joyce:Guru!D2:D20)) --ron |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need a short cut for this...
Hi Ron,
Is there any way to use existing excel 2003 functions ! bcos, if I use the "threeD", I need to send the pack to all other users of this file. It is a common file to be used by many in my office/managers at other cities. Try me some trick in Excel 2003 itself ! please x 10 million. "Ron Rosenfeld" wrote: On Wed, 15 Feb 2006 05:28:29 -0800, Eddy Stan wrote: I have three ranges in different sheets, for a selected luk up CODE 1003 I get total qty 1600 by =SUMIF($C$5:$C$10,$C3,D$5:D$10)+SUMIF(GURU!$C$3:$ C$8,SAM!$C3,GURU!D$3:D$8)+SUMIF(JOYCE!$C$3:$C$8,SA M!$C3,JOYCE!D$3:D$8) Amount 4600 by SUMIF($C$5:$C$10,$C3,F$5:F$10)+SUMIF(GURU!$C$3:$C $8,SAM!$C3,GURU!F$3:F$8)+SUMIF(JOYCE!$C$3:$C$8,SAM !$C3,JOYCE!F$3:F$8) is there any better way, than repeating 3 times sumif. Here Sam, Guru & Joyce are sheet names (may be if can name the ranges, is it possible to locate qty column & total for specific query code, likewise for amount also.) In real data will be of 300 to 500 rows in each sheet. QUERY CODE QTY AMOUNT 1003 1600 4600 SAM CODE QTY PRICE AMOUNT 1001 200 2 400 1004 300 2 600 1003 400 2 800 1004 500 2 1000 1003 600 2 1200 GURU CODE QTY PRICE AMOUNT 1001 200 2 400 1002 200 2 400 1003 200 3 600 1002 200 2 400 1003 200 5 1000 JOYCE CODE QTY PRICE AMOUNT 1005 200 2 400 1004 200 2 400 1003 200 5 1000 1004 200 2 400 1005 200 2 400 thanks in advance. Unfortunately, SUMIF is not a function that is usable with a 3D reference. However, Longre's free morefunc.xll add-in is available at http://xcell05.free.fr You can then use the THREED function to create an array from a 3D reference, and then use this formula: Assumes that your data table is in A1:D20 on each sheet. Joyce and Guru are the first and last sheets in the range. CODE being looked up is in A2 on the Active Sheet. Quantity =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THRE ED(Joyce:Guru!B2:B20)) Amount =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THRE ED(Joyce:Guru!D2:D20)) --ron |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need a short cut for this...
Eddy,
It would be possible to write a UDF to perform what you want. But why re-invent the wheel if you don't have to? Morefunc has an option to include itself in the workbook. You may include only the functions, or also include the help file. My recommendation will be to install Morefunc; then you will see in the Excel Tools menu (top menu bar) a Morefunc option. One of the selections will be "Include Morefunc in the Workbook" This would mean that if you are distributing the Workbook, Morefunc would go along with it. On Thu, 16 Feb 2006 21:47:27 -0800, Eddy Stan wrote: Hi Ron, Is there any way to use existing excel 2003 functions ! bcos, if I use the "threeD", I need to send the pack to all other users of this file. It is a common file to be used by many in my office/managers at other cities. Try me some trick in Excel 2003 itself ! please x 10 million. "Ron Rosenfeld" wrote: On Wed, 15 Feb 2006 05:28:29 -0800, Eddy Stan wrote: I have three ranges in different sheets, for a selected luk up CODE 1003 I get total qty 1600 by =SUMIF($C$5:$C$10,$C3,D$5:D$10)+SUMIF(GURU!$C$3:$ C$8,SAM!$C3,GURU!D$3:D$8)+SUMIF(JOYCE!$C$3:$C$8,SA M!$C3,JOYCE!D$3:D$8) Amount 4600 by SUMIF($C$5:$C$10,$C3,F$5:F$10)+SUMIF(GURU!$C$3:$C $8,SAM!$C3,GURU!F$3:F$8)+SUMIF(JOYCE!$C$3:$C$8,SAM !$C3,JOYCE!F$3:F$8) is there any better way, than repeating 3 times sumif. Here Sam, Guru & Joyce are sheet names (may be if can name the ranges, is it possible to locate qty column & total for specific query code, likewise for amount also.) In real data will be of 300 to 500 rows in each sheet. QUERY CODE QTY AMOUNT 1003 1600 4600 SAM CODE QTY PRICE AMOUNT 1001 200 2 400 1004 300 2 600 1003 400 2 800 1004 500 2 1000 1003 600 2 1200 GURU CODE QTY PRICE AMOUNT 1001 200 2 400 1002 200 2 400 1003 200 3 600 1002 200 2 400 1003 200 5 1000 JOYCE CODE QTY PRICE AMOUNT 1005 200 2 400 1004 200 2 400 1003 200 5 1000 1004 200 2 400 1005 200 2 400 thanks in advance. Unfortunately, SUMIF is not a function that is usable with a 3D reference. However, Longre's free morefunc.xll add-in is available at http://xcell05.free.fr You can then use the THREED function to create an array from a 3D reference, and then use this formula: Assumes that your data table is in A1:D20 on each sheet. Joyce and Guru are the first and last sheets in the range. CODE being looked up is in A2 on the Active Sheet. Quantity =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THRE ED(Joyce:Guru!B2:B20)) Amount =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THRE ED(Joyce:Guru!D2:D20)) --ron --ron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need a short cut for this...
Wow I didn't notice that option.
I am a kid in your circle, but I a genious in my circle. Thanks a million Mr Ron. Saturday/sunday was holiday for me hence the delay to reply. "Ron Rosenfeld" wrote: Eddy, It would be possible to write a UDF to perform what you want. But why re-invent the wheel if you don't have to? Morefunc has an option to include itself in the workbook. You may include only the functions, or also include the help file. My recommendation will be to install Morefunc; then you will see in the Excel Tools menu (top menu bar) a Morefunc option. One of the selections will be "Include Morefunc in the Workbook" This would mean that if you are distributing the Workbook, Morefunc would go along with it. On Thu, 16 Feb 2006 21:47:27 -0800, Eddy Stan wrote: Hi Ron, Is there any way to use existing excel 2003 functions ! bcos, if I use the "threeD", I need to send the pack to all other users of this file. It is a common file to be used by many in my office/managers at other cities. Try me some trick in Excel 2003 itself ! please x 10 million. "Ron Rosenfeld" wrote: On Wed, 15 Feb 2006 05:28:29 -0800, Eddy Stan wrote: I have three ranges in different sheets, for a selected luk up CODE 1003 I get total qty 1600 by =SUMIF($C$5:$C$10,$C3,D$5:D$10)+SUMIF(GURU!$C$3:$ C$8,SAM!$C3,GURU!D$3:D$8)+SUMIF(JOYCE!$C$3:$C$8,SA M!$C3,JOYCE!D$3:D$8) Amount 4600 by SUMIF($C$5:$C$10,$C3,F$5:F$10)+SUMIF(GURU!$C$3:$C $8,SAM!$C3,GURU!F$3:F$8)+SUMIF(JOYCE!$C$3:$C$8,SAM !$C3,JOYCE!F$3:F$8) is there any better way, than repeating 3 times sumif. Here Sam, Guru & Joyce are sheet names (may be if can name the ranges, is it possible to locate qty column & total for specific query code, likewise for amount also.) In real data will be of 300 to 500 rows in each sheet. QUERY CODE QTY AMOUNT 1003 1600 4600 SAM CODE QTY PRICE AMOUNT 1001 200 2 400 1004 300 2 600 1003 400 2 800 1004 500 2 1000 1003 600 2 1200 GURU CODE QTY PRICE AMOUNT 1001 200 2 400 1002 200 2 400 1003 200 3 600 1002 200 2 400 1003 200 5 1000 JOYCE CODE QTY PRICE AMOUNT 1005 200 2 400 1004 200 2 400 1003 200 5 1000 1004 200 2 400 1005 200 2 400 thanks in advance. Unfortunately, SUMIF is not a function that is usable with a 3D reference. However, Longre's free morefunc.xll add-in is available at http://xcell05.free.fr You can then use the THREED function to create an array from a 3D reference, and then use this formula: Assumes that your data table is in A1:D20 on each sheet. Joyce and Guru are the first and last sheets in the range. CODE being looked up is in A2 on the Active Sheet. Quantity =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THRE ED(Joyce:Guru!B2:B20)) Amount =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THRE ED(Joyce:Guru!D2:D20)) --ron --ron |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need a short cut for this...
On Sun, 19 Feb 2006 20:52:26 -0800, Eddy Stan
wrote: Wow I didn't notice that option. I am a kid in your circle, but I a genious in my circle. Thanks a million Mr Ron. Saturday/sunday was holiday for me hence the delay to reply. Well, I'm happy we developed a satisfactory solution for your problem. You may find that morefunc has a number of useful other functions, too. I hope you enjoyed your holiday. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| key board short cut for insert cut cells | Excel Discussion (Misc queries) | |||
| I can't save long file names, only short one, how can I do this? | Excel Discussion (Misc queries) | |||
| Return a block of text based on a single "short name" entry | Excel Worksheet Functions | |||
| cut & paste with short cut keys (ctrl C V) with absolute cell refe | Excel Discussion (Misc queries) | |||
| Status Bar - Key Board Short Cut | Excel Discussion (Misc queries) | |||
Hybrid Mode
