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