E
Eddy Stan
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.
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.