Need a short cut for this...

Eddy Stan · 02-15-2006, 09:30 AM

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.

Ron Rosenfeld · 02-15-2006, 10:45 AM

On Wed, 15 Feb 2006 05:28:29 -0800, Eddy Stan
<EddyStan@discussions.microsoft.com> 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,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.

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)*THREED(Joyce:Guru!B2:B20))

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

--ron

Eddy Stan · 02-17-2006, 01:50 AM

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
> <EddyStan@discussions.microsoft.com> 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,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.
>
>
> 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)*THREED(Joyce:Guru!B2:B20))
>
> Amount
> =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THREED(Joyce:Guru!D2:D20))
>
>
> --ron
>

Ron Rosenfeld · 02-17-2006, 08:30 AM

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
<EddyStan@discussions.microsoft.com> 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
>> <EddyStan@discussions.microsoft.com> 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,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.
>>
>>
>> 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)*THREED(Joyce:Guru!B2:B20))
>>
>> Amount
>> =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THREED(Joyce:Guru!D2:D20))
>>
>>
>> --ron
>>

--ron

Eddy Stan · 02-20-2006, 12:55 AM

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
> <EddyStan@discussions.microsoft.com> 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
> >> <EddyStan@discussions.microsoft.com> 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,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.
> >>
> >>
> >> 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)*THREED(Joyce:Guru!B2:B20))
> >>
> >> Amount
> >> =SUMPRODUCT((THREED(Joyce:Guru!$A2:$A20)=$A2)*THREED(Joyce:Guru!D2:D20))
> >>
> >>
> >> --ron
> >>
>
> --ron
>

Ron Rosenfeld · 02-20-2006, 08:40 AM

On Sun, 19 Feb 2006 20:52:26 -0800, Eddy Stan
<EddyStan@discussions.microsoft.com> 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