# Sum across dynamic sheet names if criteria met on summary sheet

#### xoScarecrowox

##### New Member
Hi all, many attempts at this one but unable to solve. So I have workbook with a varying number of worksheets which are listed in column A, column B has a Yes or No option pulldown, what I am attempting to do is sum the values from A1 of each sheet if column B is Yes, if No then exclude from sum. If we assume I have the number 10 in A1 of each worksheet, I want a formula that will look at column B for a Yes then sum the value from the sheet name in the adjacent cell A, the sum will be in cell C2.

What I have so for is:
=IF(B2="Yes",INDIRECT("'"&A2&"'!A1"))+IF(B3="Yes",INDIRECT("'"&A3&"'!A1"))+IF(B4="Yes",INDIRECT("'"&A4&"'!A1")) etc,etc.
This formula does work but the list of sheets could be extensive and the formula could be very lengthy so I am wondering if there is a better formula that would achieve the same result.
Something like =IF(B2:B="Yes", INDIRECT("'"&A2:A&"'!A1")) although this does not work.

Any advise would be greatly appreciated

 Sheet name Include Total from sheets Sheet2 Yes =IF(B2="Yes",INDIRECT("'"&A2&"'!A1"))+IF(B3="Yes",INDIRECT("'"&A3&"'!A1"))+IF(B4="Yes",INDIRECT("'"&A4&"'!A1")) Sheet3 Yes Sheet4 No Sheet5 No Sheet6 No Sheet7 Yes Sheet8 No Sheet9 No Sheet10 Yes

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Domenic

##### MrExcel MVP
First define the following name (Ribbon >> Formulas >> Defined Names >> Define Name) . . .

VBA Code:
``````Name: MyArr

Refers to: =EVALUATE("{"&TEXTJOIN(",",TRUE,IF(Sheet1!\$B\$2:\$B\$10="Yes",""""&Sheet1!\$A\$2:\$A\$10&"""",""))&"}")``````

Adjust the ranges accordingly. Then try the following...

VBA Code:
``=SUMPRODUCT(N(INDIRECT("'"&MyArr&"'!A1")))``

Hope this helps!

#### xoScarecrowox

##### New Member
That worked brilliantly, thank you ever so much.

Would there possibly be something that could be added to the code that would put "0" in cell if all of B2:B10 was set to "No", currently it gives #VALUE.

#### Domenic

##### MrExcel MVP
Sure, we can amend the formula as follows...

=IF(COUNTIF(\$B\$2:\$B\$10,"Yes")>0,SUMPRODUCT(N(INDIRECT("'"&MyArr&"'!A1"))),0)

#### xoScarecrowox

##### New Member

Outstanding, thanks again, you have saved me so much time and its good to learn from the experts.

#### Domenic

##### MrExcel MVP
You're very welcome, glad to help.

Cheers!

#### xoScarecrowox

##### New Member

Not sure if I can continue this thread but I have encountered a problem with the method supplied, it would seem that it will not run on "Teams" as it does not support named ranges or macros. The problem is there will be multiple colleagues using the sheet at the same time and Teams is the best option we have for shared documents.

Is there another method that avoids the use of a named range or macro?
I thought something like this may work but it seems to ignore the Yes/No selection and sums the sheet values no matter what is selected.

=IF(COUNTIF(\$B\$2:\$B\$10,"Yes")>0,SUMPRODUCT(SUM(N(INDIRECT(A2:A10&"!A1")))),0)

#### Dave Patton

##### Well-known Member
I am not sure of your requirement. An example would help.

Review and edit the following.

I show the total of the cells in A1with Yes in B1 and I also show the result by sheet.
The named range is only for illustration purposes.

3D Sumif.xlsm
ABCDE
1SheetsYES4545Total
21aYES10By Sheet
31bYES0By Sheet
41cYES15By Sheet
51dYES0By Sheet
61eYES20By Sheet
Summary
Cell Formulas
RangeFormula
C1C1=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"B1"),B1,INDIRECT("'"&Sheets&"'!"&"A1")))
D1D1=SUMPRODUCT(SUMIF(INDIRECT("'"&\$A\$2:\$A\$6&"'!"&"B1"),B1,INDIRECT("'"&\$A\$2:\$A\$6&"'!"&"A1")))
D2:D6D2=SUMPRODUCT(SUMIF(INDIRECT("'"&\$A2&"'!"&"B1"),B2,INDIRECT("'"&\$A2&"'!"&"A1")))
Named Ranges
NameRefers ToCells
Sheets=Summary!\$A\$2:\$A\$6C1:D1, D2

#### Dave Patton

##### Well-known Member
With 10 in A1 for each sheet and just using Sumif

3D Sumif.xlsm
ABCD
1SheetsYES30Total
21aYES10By Sheet
31bYES0By Sheet
41cYES10By Sheet
51dYES0By Sheet
61eYES10By Sheet
7
Summary
Cell Formulas
RangeFormula
C1C1=SUMPRODUCT(SUMIF(INDIRECT("'"&\$A\$2:\$A\$6&"'!"&"B1"),B1,INDIRECT("'"&\$A\$2:\$A\$6&"'!"&"A1")))
C2:C6C2=SUMIF(INDIRECT("'"&\$A2&"'!"&"B1"),B2,INDIRECT("'"&\$A2&"'!"&"A1"))
Named Ranges
NameRefers ToCells
Sheets=Summary!\$A\$2:\$A\$6C1:C2

#### xoScarecrowox

##### New Member
With 10 in A1 for each sheet and just using Sumif

3D Sumif.xlsm
ABCD
1SheetsYES30Total
21aYES10By Sheet
31bYES0By Sheet
41cYES10By Sheet
51dYES0By Sheet
61eYES10By Sheet
7
Summary
Cell Formulas
RangeFormula
C1C1=SUMPRODUCT(SUMIF(INDIRECT("'"&\$A\$2:\$A\$6&"'!"&"B1"),B1,INDIRECT("'"&\$A\$2:\$A\$6&"'!"&"A1")))
C2:C6C2=SUMIF(INDIRECT("'"&\$A2&"'!"&"B1"),B2,INDIRECT("'"&\$A2&"'!"&"A1"))
Named Ranges
NameRefers ToCells
Sheets=Summary!\$A\$2:\$A\$6C1:C2
Hi Dave

Thanks for the fast response

I have set up a sheet as per your reply but I am not getting what you get.

Each cell is exactly as you have in your cells, one thing I am unsure about is 'Named range' and your cell reference, I have created named range called 'Sheets' enter formula in refers to box, I don't see Cells as an option though.

What I get is this;

 Sheets YES 0 0​ 1a YES 0​ 1b YES 0​ 1c YES 0​ 1d YES 0​ 1e YES 0​

 Cell Formulas Range Cell Formula C1 C1 =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"B1"),B1,INDIRECT("'"&Sheets&"'!"&"A1"))) D1 D1 =SUMPRODUCT(SUMIF(INDIRECT("'"&\$A\$2:\$A\$6&"'!"&"B1"),B1,INDIRECT("'"&\$A\$2:\$A\$6&"'!"&"A1"))) D2:D6 D2 =SUMIF(INDIRECT("'"&\$A2&"'!"&"B1"),B2,INDIRECT("'"&\$A2&"'!"&"A1")) D3 =SUMIF(INDIRECT("'"&\$A3&"'!"&"B1"),B3,INDIRECT("'"&\$A3&"'!"&"A1")) D4 =SUMIF(INDIRECT("'"&\$A4&"'!"&"B1"),B4,INDIRECT("'"&\$A4&"'!"&"A1")) D5 =SUMIF(INDIRECT("'"&\$A5&"'!"&"B1"),B5,INDIRECT("'"&\$A5&"'!"&"A1")) D6 =SUMIF(INDIRECT("'"&\$A6&"'!"&"B1"),B6,INDIRECT("'"&\$A6&"'!"&"A1"))

#### Attachments

• Capture.JPG
18.4 KB · Views: 3

Replies
40
Views
858
Replies
6
Views
230
Replies
1
Views
175
Replies
8
Views
365
Replies
0
Views
124

1,148,283
Messages
5,745,842
Members
423,981
Latest member
ph1l

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back