pulling from multiple worksheets (SUMIF) with a single criteria

M

MBrew63

I am trying to do a SUMIF or VLOOKUP.

If A2=X, find X listed in the following worksheet columns

(Solid!$A$2:$A$65536)
(FingerJoint!$A$2:$A$65536)
(Prime!$A$2:$A$65536)
(Oak!$A$2:$A$65536)
(MDF!A$2:$A$65536)
(PVC!$A$2:$A$65536)

IF X is found in one of the worksheet ranges above, the put the pric
that coorespons to X in. The price would be found in these columns i
the respective worksheets

(Solid!$I$9:$I$65536)
(FingerJoint!$I$9:$I$65536)
(Prime!$I$9:$I$65536)
(Oak!$I$9:$I$65536)
(MDF!$I$9:$I$65536)
(PVC!$I$9:$I$65536)

I believe that I am trying to do a SUMIF. This is what I have come u
with so far, but it is not working.

=SUMIF(((Solid!$A$12:$A$65536)(FingerJoint!$A$12:$A$65536)(Prime!$A$12:$A$65536)(Oak!$A$12:$A$65536)(MDF!A$12:$A$65536)(PVC!$A$12:$A$65536)),$A2,((Solid!$I$9:$I$65536)(FingerJoint!$I$9:$I$65536)(Prime!$I$9:$I$65536)(Oak!$I$9:$I$65536)(MDF!$I$9:$I$65536)(PVC!$I$9:$I$65536)))

I need help. I frustrated!

MBrew6
 
M

MBrew63

I just realized that is is actually supposed to be $I$12:.... and not
the $I$9:...., but I still have my problem about how to set up the
function to look in multiple places.

MBrew63
 
F

Frank Kabel

Hi
some solutions:
1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. e.g. you may use the following formula on your
sheet
=SUM((THREED('Solid!:pVC'!A1:A10000)=A2)*(THREED('Solid!:pVC'!I1:I10000
)))
 
Top