Option 1 worked like a champ and was EXACTLY what I needed!! Thanks a million!!!
Two options...
1]
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10")))
where SheetList refers to a range that lists the relevant sheet names, A2:B10 the relevant range in every sheet in SheetList, and D2 the criterion value.
2] Using the morefunc.xll add-in...
=SUMPRODUCT(--(THREED(Sheet2:Sheet3!$A$2:$A$10)=D2),(THREED(Sheet2:Sheet3!$B$2:$B$10)))
where Sheet2 and Sheet3 are the relevant sheets while A2:B10 is the relevant range and D2 the criterion.
i tried both variables below
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A10"),D2,INDIRECT("'"&SheetList&"'!B2:B10")))
2] Using the morefunc.xll add-in...
=SUMPRODUCT(--(THREED(Sheet2:Sheet3!$A$2:$A$10)=D2),(THREED(Sheet2:Sheet3!$B$2:$B$10)))
yes i want the sum of telephone bills from January to December
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!C2:C18"),C2,INDIRECT("'"&SheetList&"'!E2:E18")))
If you don't know how to name a range, invoke:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$13&"'!C2:C18"),C2,INDIRECT("'"&$A$2:$A$13&"'!E2:E18")))
2CSH34029-6001 | Column1 | JOB # | JOB #2056 | TOTAL |
PART # | DESCRIPTION | QTY | 1 | 1 |
2CSH34029-0013 | FRAME - FS 402, UPPER RHS | 1 | 1 | 1 |
LMT02932 | AL PLATE | 1 | 1 | 1 |
EN1182ND6 | NUT | 1 | 1 | 1 |
JSFF17D24 | COUPLING | 1 | 1 | 1 |
JSFM20-60 | STANDOFF | 1 | 1 | 1 |
LMT00064 | FUEL TANK SEALANT | 1 | 1 | 1 |
MS20470AD5-6 | RIVET | 2 | 2 | 2 |
VS8008NN6-2-11 | PIN | 1 | 1 | 1 |
PART # | DESCRIPTION | Column1 |
2CBH31621-0001 | CLIP ASSY, COAX | 0 |
2CBH31622-0001 | CLIP ASSY, COAX, FUEL FLOOR, FWD | 0 |
2CBH31622-2001 | CLIP, COAX, FUEL FLOOR, FWD | 0 |
2CBH31623-0001 | CLIP ASSY, COAX, FUEL FLOOR, AFT | 0 |
2CBH31623-2001 | CLIP, COAX, FUEL FLOOR, AFT | 0 |
2CBH32008-0005 | CLIP ASSY | 0 |
=SUMIF(INDIRECT("'"&SHEETLIST&"'!A2:A415"),A187,INDIRECT("'"&SHEETLIST&"'!Table5[TOTAL]"))
This formula works, I just discovered. I've tested it on one sheet and it has returned the correct value from that one sheet. The next step is to get it to sum across all other sheets and tables. Would I need to list each table name or is there a way to reference them all like at once?
Not sure I follow...
SHEETLIST must refer to a range in which the relevant sheet names are located.
Table5[TOTAL] must be equal in size to A2:A415.