# 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

#### Dave Patton

##### Well-known Member
You stated that your range of sheet names is one cell lower; I edited the formula to reflect that.

I also added the text for Yes in B3

Try the formula that I suggested!!!

=IF(B3="Yes",SUMPRODUCT(SUMIF(INDIRECT("'"&\$A\$3:\$A\$10&"'!"&"B3"),"yes",INDIRECT("'"&\$A\$3:\$A\$10&"'!"&"F3"))),"")

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### xoScarecrowox

##### New Member
You stated that your range of sheet names is one cell lower; I edited the formula to reflect that.

I also added the text for Yes in B3

Try the formula that I suggested!!!

=IF(B3="Yes",SUMPRODUCT(SUMIF(INDIRECT("'"&\$A\$3:\$A\$10&"'!"&"B3"),"yes",INDIRECT("'"&\$A\$3:\$A\$10&"'!"&"F3"))),"")
SUM SHEETS IF YES1.xlsm
ABCDEFGHIJK
1Data in each sheet
2TestYES/NOSUM of sheets F3 with YES in column BTest Case Results
3Blocking Element (BE)YES0U3991%1 h. 46 m.YES
4wAppLoxxYES234P12%1 h. 00 m.NO
5CamerasNOF00%0 h. 00 m.
6RepeaterNOS37%0 h. 00 m.
7ARC RedundancyYESO00%0 h. 00 m.
8Keypad LED IndicationYESN/A00%0 h. 00 m.
9GDPR + OSSYESTotal Tested12%1 h. 00 m.
10Special Keypad (Log Delete)YES% of test complete9%
11etc,etc,etc,,,,,,NO,NO,NO,,,,,Total tasks for Test432 h. 46 m.
Summary
Cell Formulas
RangeFormula
C3C3=IF(B3="Yes",SUMPRODUCT(SUMIF(INDIRECT("'"&\$A\$3:\$A\$10&"'!"&"B3"),"yes",INDIRECT("'"&\$A\$3:\$A\$10&"'!"&"F3"))),"")
C4C4=IF(B3="YES",INDIRECT("'"&A\$3&"'!"&"F3"))+IF(B4="YES",INDIRECT("'"&A\$4&"'!"&"F3"))+IF(B5="YES",INDIRECT("'"&A\$5&"'!"&"F3"))+IF(B6="YES",INDIRECT("'"&A\$6&"'!"&"F3"))+IF(B7="YES",INDIRECT("'"&A\$7&"'!"&"F3"))+IF(B8="YES",INDIRECT("'"&A\$8&"'!"&"F3"))+IF(B9="YES",INDIRECT("'"&A\$9&"'!"&"F3"))+IF(B10="YES",INDIRECT("'"&A\$10&"'!"&"F3"))
Cells with Data Validation
CellAllowCriteria
B3:B11List=\$K\$3:\$K\$4

Still get 0
Your formula only seems to consider the state of B3 to perform the function where it needs to calculate the result with consideration to B3:B10,the formula I have in C3 should explain it better as that works as it should but is long.

#### Dave Patton

##### Well-known Member
If you want additional suggestions, prepare a very condensed model of what you require and post it with XL2BB.

Say 2 data sheets and a summary sheet
5 rows of data
An explanation of what you want to achieve and expected results.

The mini model could then be expanded for your actual data.

#### xoScarecrowox

##### New Member
This is what's in the summary sheet:
SUM SHEETS IF YES1.xlsm
ABCDEFGIJ
1
2Test processTest required 'Yes' or 'NoTC CountRemaining Tests to be completedTest TimeTest Time RemainingDropdown B3:B5
3Blocking Element (BE)YES43392 h. 46 m.1 h. 46 m.YES
4wAppLoxxNO----NO
5CamerasYES43392 h. 46 m.1 h. 46 m.
6RepeaterNO----
7ARC RedundancyNO----
8
9
10Test Results Table
11Test Result StatusTC Count% CountTest Time
12
13Untested7891%3 h. 32 m.
14Passed22%2 h. 00 m.
15Failed00%0 h. 00 m.
16Skipped67%0 h. 00 m.
17Observation00%0 h. 00 m.
18N/A00%0 h. 00 m.
19
20Total86100%5 h. 32 m.
21
Summary
Cell Formulas
RangeFormula
C3:C7C3=IF(B3="No","-",INDIRECT("'"&A3&"'!F11"))
D3:D7D3=IF(B3="No","-",INDIRECT("'"&A3&"'!F3"))
E3:E7E3=IF(B3="No","-",INDIRECT("'"&A3&"'!H11"))
F3:F7F3=IF(B3="No","-",INDIRECT("'"&A3&"'!H3"))
D13D13=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"F3"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"F3"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"F3"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"F3"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"F3"),0)
E13:E18E13=D13/\$D\$20
F13F13=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"H3"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"H3"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"H3"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"H3"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"H3"),0)
D14D14=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"F4"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"F4"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"F4"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"F4"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"F4"),0)
F14F14=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"H4"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"H4"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"H4"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"H4"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"H4"),0)
D15D15=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"F5"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"F5"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"F5"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"F5"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"F5"),0)
F15F15=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"H5"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"H5"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"H5"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"H5"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"H5"),0)
D16D16=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"F6"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"F6"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"F6"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"F6"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"F6"),0)
F16F16=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"H6"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"H6"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"H6"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"H6"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"H6"),0)
D17D17=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"F7"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"F7"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"F7"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"F7"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"F7"),0)
F17F17=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"H7"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"H7"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"H7"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"H7"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"H7"),0)
D18D18=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"F8"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"F8"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"F8"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"F8"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"F8"),0)
F18F18=IF(\$B\$3="YES",INDIRECT("'"&A\$3&"'!"&"H8"),0)+IF(\$B\$4="YES",INDIRECT("'"&A\$4&"'!"&"H8"),0)+IF(\$B\$5="YES",INDIRECT("'"&A\$5&"'!"&"H8"),0)+IF(\$B\$6="YES",INDIRECT("'"&A\$6&"'!"&"H8"),0)+IF(\$B\$7="YES",INDIRECT("'"&A\$7&"'!"&"H8"),0)
D20,F20D20=SUM(C3:C7)
E20E20=SUM(E13:E18)
Cells with Data Validation
CellAllowCriteria
B3:B7List=\$J\$3:\$J\$4

This is what's in the individual sheets:
SUM SHEETS IF YES1.xlsm
ABCDEFGHI
1Summary of progress
2Test Case Results
3Untested3991%1 h. 46 m.
4Passed12%1 h. 00 m.
5Failed00%0 h. 00 m.
6Skipped37%0 h. 00 m.
7Observation00%0 h. 00 m.
8N/A00%0 h. 00 m.
9Total Tested12%1 h. 00 m.
10% of test complete9%
11Total tasks for Test432 h. 46 m.
12
13
14TC# Test Execution Steps Expected ResultTest ResultDate Tested TesterTC TimeComment / (or Requirement xref)
15User Story -
161Configure Panel/System as per "Basic Test Set Up"No expected outcomeP60 m
172Wire Blocking Element to panel (see below wiring configuration): Red > Panel 12V Aux Blue > Panel 0V Aux Brown > Panel Wired Output 3 Yellow > Panel 12V Aux Green > Panel 0V Aux White > Panel Wired Zone 008 (2-Wire CC / Terminal 2 to Ground) Black > Not used No expected outcomeU10 m
183Power the panel/system up and enter engineering modePanel powers up and engineering mode sucessfully enteredU2 m
Blocking Element (BE)
Cells with Data Validation
CellAllowCriteria
D16:D18ListU,P,F,O,S,n/a
A14:B14Any value
D14Any value

The summary sheet pulls data from the table at the top of each sheet (E3:E11) named in A3:A7
Cells D13:D18 is the formula that I am looking to replace with a more compact formula, the reason being is that the amount of sheets will vary and therefore the formula I currently use will be very lengthy.

To explain the sheet, it is a universal test plan and not all tests will be required, as in not all sheets will be pulled to the summary sheet, the choice of this is made by choosing 'YES' or 'No' in B3:B7.
Cells D13:D18 is a SUM of the tables from each sheet but only the sheets I have chosen by choosing 'YES' in cells B3:B7.

The example I have attached, on the 'Summary' sheet you will see that I have selected 'YES' against Blocking Element (BE) and Cameras so the data in D13:D18 has only summed data from those sheets and not included the sheets where I have selected 'NO'.

Hope this all makes sense.

I would also like to add that the solution that was provided at the beginning of this thread worked exactly the way I needed but the problem is the sheet will be used on 'Teams' and 'Named ranges' do not work on 'Teams' as well as macros do not run on Teams.

#### Dave Patton

##### Well-known Member

The structure of a workbook can expedite data analysis and reporting or make it more difficult to secure quality information. In many situations, a sheet for Data and another for reporting is efficient. The data may be structured similar to a database.

In limited situations when the data is consistent multiple sheets can be used. Analysis of overall results may be more difficult. I will post two ways some reporting can be completed when data sheets are structured exactly the same. Neither of these solutions is dynamic; with one the relevant sheets must be put between a Start sheet and an End sheet and with the other examples the relevant sheet names must be in an array or range and the formulas edited unless a named range or named array is used.

With relevant data sheets between a blank sheet named Start and a blank sheet name End. We can use formulas like =SUM(Start:End!C2) see the post

A second alternative is to put the names of the relevant sheets in an array or range of cells for example G2:G3 and use formulas like =SUMPRODUCT(N(INDIRECT("'"&\$G\$2:\$G\$3&"'!"&CELL("address",C2))))
View1 and View2 show sum of the 2 sheets Test1 and Test2. The two use different methods to calculate the sum.

View3 includes Test1, Test2, and Test3 The Sheets list is extended to G2:G4 and the formula is edited to include this range.

If you name the range G2:G4, the formula could include the Named Range and you would just have to edit the range. If you do not want to include the sheet names in a column, you can use the array of names or name the array {"Test1";"Test2";"Test3"} see C6.

Named ranges or named information

Sheets =View3!\$G\$2:\$G\$4

Sheet2 ={"Test1";"Test2";"Test3"}

see next post

#### Dave Patton

##### Well-known Member
To try the examples cited:

- open a new workbook

- create sheets and name them in the order stated

- click on the icon below the f(x) on the post and paste the information into the respective sheets

- review the formulas on the Report sheets (View1, View2, or View3)

 Sheets are in the order shown below. Description File Sheet Blank sheet Test Results Start data sheet Test Results Test1 data sheet Test Results Test2 blank sheet Test Results End Data sheet Test Results Test3 Report v1 Test Results View1 Report v2 Test Results View2 Report v3 Test Results View3 information Test Results Notes

#### Dave Patton

##### Well-known Member

Test Results.xlsm
ABC
1DateSyResult
218-07-21A12
318-07-21B14
418-07-21C16
518-07-21D18
618-07-21E20
7
Test1

Test Results.xlsm
ABC
1DateSyResult
218-07-21A13.2
318-07-21B15.4
418-07-21C17.6
518-07-21D19.8
618-07-21E22
7
Test2

Test Results.xlsm
ABC
1DateSyResult
218-07-21A132
318-07-21B154
418-07-21C176
518-07-21D198
618-07-21E220
7
Test3

#### Dave Patton

##### Well-known Member
Test Results.xlsm
ABC
1SyResult
2A25.2
3B29.4
4C33.6
5D37.8
6E42
7
View1
Cell Formulas
RangeFormula
C2:C6C2=SUM(Start:End!C2)

#### Dave Patton

##### Well-known Member
Test Results.xlsm
ABC
1SyTotal
2A25.2
3B29.4
4C33.6
5D37.8
6E42
7
View2
Cell Formulas
RangeFormula

#### Dave Patton

##### Well-known Member
Test Results.xlsm
ABC
1SyTotal
2A157.2
3B183.4
4C209.6
5D235.8
6E262.0
7
View3
Cell Formulas
RangeFormula
Named Ranges
NameRefers ToCells
Sheets=View3!\$G\$2:\$G\$4C2:C5

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

1,148,276
Messages
5,745,806
Members
423,976
Latest member

### 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