Formula with Text

[Rob #4]

How can I correct this formula:

=SUMIF('001'!A21:A31,"Labor",'001'!G21:G31)+SUMIF('002'!A21:A31,"Labor",'002'!G21:G31)+SUMIF('003'!A21:A31,"Labor",'003'!G21:G31)

I want to remove the "Labor" since I have Labor typed into this cell 'Cat&Markups'!A2, so I want it to reference that cell instead.

Also, is there an easier formula for doing what I am trying to do between worksheets?

 

[Special-K99]

Just replace it in the formula, should work

=SUMIF('001'!A21:A31,'Cat&Markups'!A2,'001'!G21:G31)+SUMIF('002'!A21:A31,'Cat&Markups'!A2,'002'!G21:G31)+SUMIF('003'!A21:A31,'Cat&Markups'!A2,'003'!G21:G31)

3D SUMIF info here

https://exceljet.net/formula/3d-sumif-for-multiple-worksheets

https://www.myonlinetraininghub.com/excel-3d-sumif-across-multiple-worksheets

 

[Rob #4]

So this is the formula I put together after looking at those links, but it returns #NAME ?

=SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!A21:A31"),'Cat&Markups'!A2,INDIRECT("'"&tabs&"'!G21:G31")))

 

[Special-K99]

#NAME means you've either referenced a formula that doesnt exist, e.g SMPRDCT instead of SUMPRODUCT
but as the formula looks ok I would suggest that

tabs

hasn't been defined in the name manager or tabs refers to sheets that may not exist (I'm guessing here).

To be honest I haven't had a close look at those links!
I'll do some testing

 

[Rob #4]

How do I add the Tabs to the name manager?

 

[Special-K99]

Works ok for me.

1. In a blank workbook create 3 sheets Sheet2 Sheet3 Sheet4
2. In those new sheets in A1:A4 put a, b, c, d - one letter in each cell going down coluimn A
3. Put some random numbers in B1:B4 on each of the new sheets
4. In Sheet1!A1 put tabs
5. in Sheet1!A2 put Sheet2
6. in Sheet1!A3 put Sheet3
7. in Sheet1!A4 put Sheet4
8. in Sheet1!C2 put

=SUMPRODUCT(SUMIF(INDIRECT("'"&tabs&"'!A1:A4"),B1,INDIRECT("'"&tabs&"'!B1:B4")))

Result is #NAME

9. Select Sheet1!A2:A4 - In Formulas tab go to Name Manager and define the selected area as tabs

Put a letter a b c or d in Sheet1!B1

C2 should now display the sum of the matched data from Sheet2 Sheet3 Sheet4

 

[Special-K99]

How do I add the Tabs to the name manager?

You're defining a name that relates to a selected area of a spreadsheet.
Select the area that list the different Sheets

Go to Formulas,
halfway along screen says Name Manager.
Either click that or click Define Name and type tabs and OK it.

The #NAME error should disappear and you should get a valid result as long as 'Cat&Markups'!A2 matches a value in the other sheets

 

[Rob #4]

It got it now. Will this formula correct itself if I add or delete tabs?

 

[Special-K99]

Extend the range in the formula and redefine the range in the Name Manager to reflect this.

So if the range of sheets named is in A2:A4 just add/delete sheets as required and change the formula to A2:A50
and define a Named range in the name Manager as A2:A50 to reflect that