Need a short cut for this...

[Guest]

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$10)+SUMIF(GURU!$C$3:$C$8,SAM!$C3,GURU!D$3$8)+SUMIF(JOYCE!$C$3:$C$8,SAM!$C3,JOYCE!D$3$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.

 

[Ron Rosenfeld]

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$10)+SUMIF(GURU!$C$3:$C$8,SAM!$C3,GURU!D$3$8)+SUMIF(JOYCE!$C$3:$C$8,SAM!$C3,JOYCE!D$3$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 A120 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)*THREED(Joyce:Guru!B2:B20))

Amount
=SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THREED(Joyce:Guru!D220))


--ron

 

[Guest]

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]

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.

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

 

[Guest]

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]

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