I am rather new to excel, some of these formulas go right over my head. I am catching on though, between googling and youtubing.
I have tried both options and have had Zero Success. I am not exactly sure why it is not working.
My current formula I am using is getting rather lengthy. Everytime I add a new page, i add more to the formula.
This is my current Lengthy Formula:
=SUMIF('BLANK PO'!$A$9:$A$41,$B6,'BLANK PO'!$I$9:$I$41)+SUMIF('LC2-0154B'!$A$9:$A$41,$B6,'LC2-0154B'!$I$9:$I$41)+SUMIF('LC3-6151A'!$A$9:$A$41,$B6,'LC3-6151A'!$I$9:$I$41)+SUMIF('LC3-6140A (P1)'!$A$9:$A$41,$B6,'LC3-6140A (P1)'!$I$9:$I$41)+SUMIF('LC3-6160'!$A$9:$A$41,$B6,'LC3-6160'!$I$9:$I$41)+SUMIF('LC3-6160NT'!$A$9:$A$41,$B6,'LC3-6160NT'!$I$9:$I$41)+SUMIF('LC3-6151NT-A'!$A$9:$A$41,$B6,'LC3-6151NT-A'!$I$9:$I$41)+SUMIF('LC3-6160A (P1)'!$A$9:$A$41,$B6,'LC3-6160A (P1)'!$I$9:$I$41)+SUMIF('LC3-6140A (P2)'!$A$9:$A$41,$B6,'LC3-6140A (P2)'!$I$9:$I$41)+SUMIF('LC3-6160A (P2)'!$A$9:$A$41,$B6,'LC3-6160A (P2)'!$I$9:$I$41)+SUMIF('LC3-6151B'!$A$9:$A$41,$B6,'LC3-6151B'!$I$9:$I$41)+SUMIF('LC3-6172'!$A$9:$A$41,$B6,'LC3-6172'!$I$9:$I$41)+SUMIF('LC3-6172NT'!$A$9:$A$41,$B6,'LC3-6172NT'!$I$9:$I$41)
I work in a place, that uses Purchase Orders when we buy something. I have to code everything under each purchase order (each purchase order is another sheet added to the workbook) depending on what we use it for. I am trying to keep track of the total costs for each code. Every sheet with the exception of the Totals page looks the same. So my range areas do not change, just my criterion and sheets.
I do not know what I am doing wrong, I get NAME and REF errors everytime I try to use the option mentioned.
As you can see with my formula, I tell it to look for a specific in ranges $A$9:$A$41, the criterion/code changes but stays put in the $B column, and when it finds a matching code it sums the price, which is found in $I$9:$I$41. I have had no luck so far with spreading it across sheets, unless I add more to the code. Just copy and paste change the sheet listed and add it together... its getting rather lengthy.
Any help would be awesome. Thanks! =)
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.