Sum across dynamic sheet names if criteria met on summary sheet

xoScarecrowox

New Member
Joined
Apr 2, 2019
Messages
15
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 nameIncludeTotal from sheets
Sheet2Yes=IF(B2="Yes",INDIRECT("'"&A2&"'!A1"))+IF(B3="Yes",INDIRECT("'"&A3&"'!A1"))+IF(B4="Yes",INDIRECT("'"&A4&"'!A1"))
Sheet3Yes
Sheet4No
Sheet5No
Sheet6No
Sheet7Yes
Sheet8No
Sheet9No
Sheet10Yes
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Apr 2, 2019
Messages
15
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
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Apr 2, 2019
Messages
15
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
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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.

SUMPRODUCT(N(INDIRECT("'"&$G$2:$G$4&"'!"&CELL("address",C2))))

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
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
DescriptionFileSheet
Blank sheetTest ResultsStart
data sheetTest ResultsTest1
data sheetTest ResultsTest2
blank sheetTest ResultsEnd
Data sheetTest ResultsTest3
Report v1Test ResultsView1
Report v2Test ResultsView2
Report v3Test ResultsView3
informationTest ResultsNotes
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Test Results.xlsm
ABC
1SyTotal
2A25.2
3B29.4
4C33.6
5D37.8
6E42
7
View2
Cell Formulas
RangeFormula
C2:C6C2=SUMPRODUCT(N(INDIRECT("'"&$G$2:$G$3&"'!"&CELL("address",C2))))
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Test Results.xlsm
ABC
1SyTotal
2A157.2
3B183.4
4C209.6
5D235.8
6E262.0
7
View3
Cell Formulas
RangeFormula
C2:C5C2=SUMPRODUCT(N(INDIRECT("'"&Sheets&"'!"&CELL("address",C2))))
C6C6=SUMPRODUCT(N(INDIRECT("'"&Sheets2&"'!"&CELL("address",C6))))
Named Ranges
NameRefers ToCells
Sheets=View3!$G$2:$G$4C2:C5
 

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top