Sum across dynamic sheet names if criteria met on summary sheet

[xoScarecrowox]

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]

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"))),"")

 

[xoScarecrowox]

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
	A	B	C	D	E	F	G	H	I	J	K
1					Data in each sheet
2	Test	YES/NO	SUM of sheets F3 with YES in column B			Test Case Results
3	Blocking Element (BE)	YES	0		U	39	91%	1 h. 46 m.			YES
4	wAppLoxx	YES	234		P	1	2%	1 h. 00 m.			NO
5	Cameras	NO			F	0	0%	0 h. 00 m.
6	Repeater	NO			S	3	7%	0 h. 00 m.
7	ARC Redundancy	YES			O	0	0%	0 h. 00 m.
8	Keypad LED Indication	YES			N/A	0	0%	0 h. 00 m.
9	GDPR + OSS	YES			Total Tested	1	2%	1 h. 00 m.
10	Special Keypad (Log Delete)	YES			% of test complete		9%
11	etc,etc,etc,,,,,,	NO,NO,NO,,,,,			Total tasks for Test	43		2 h. 46 m.
Summary

Cell Formulas
Range	Formula
C3	C3	=IF(B3="Yes",SUMPRODUCT(SUMIF(INDIRECT("'"&$A$3:$A$10&"'!"&"B3"),"yes",INDIRECT("'"&$A$3:$A$10&"'!"&"F3"))),"")
C4	C4	=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
Cell	Allow	Criteria
B3:B11	List	=$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]

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]

This is what's in the summary sheet:

SUM SHEETS IF YES1.xlsm
	A	B	C	D	E	F	G		I	J
1
2	Test process	Test required 'Yes' or 'No	TC Count	Remaining Tests to be completed	Test Time	Test Time Remaining				Dropdown B3:B5
3	Blocking Element (BE)	YES	43	39	2 h. 46 m.	1 h. 46 m.				YES
4	wAppLoxx	NO	-	-	-	-				NO
5	Cameras	YES	43	39	2 h. 46 m.	1 h. 46 m.
6	Repeater	NO	-	-	-	-
7	ARC Redundancy	NO	-	-	-	-
8
9
10	Test Results Table
11	Test Result Status			TC Count	% Count	Test Time
12
13	Untested			78	91%	3 h. 32 m.
14	Passed			2	2%	2 h. 00 m.
15	Failed			0	0%	0 h. 00 m.
16	Skipped			6	7%	0 h. 00 m.
17	Observation			0	0%	0 h. 00 m.
18	N/A			0	0%	0 h. 00 m.
19
20	Total			86	100%	5 h. 32 m.
21
Summary

Cell Formulas
Range	Formula
C3:C7	C3	=IF(B3="No","-",INDIRECT("'"&A3&"'!F11"))
D3:D7	D3	=IF(B3="No","-",INDIRECT("'"&A3&"'!F3"))
E3:E7	E3	=IF(B3="No","-",INDIRECT("'"&A3&"'!H11"))
F3:F7	F3	=IF(B3="No","-",INDIRECT("'"&A3&"'!H3"))
D13	D13	=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:E18	E13	=D13/$D$20
F13	F13	=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)
D14	D14	=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)
F14	F14	=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)
D15	D15	=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)
F15	F15	=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)
D16	D16	=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)
F16	F16	=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)
D17	D17	=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)
F17	F17	=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)
D18	D18	=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)
F18	F18	=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,F20	D20	=SUM(C3:C7)
E20	E20	=SUM(E13:E18)

Cells with Data Validation
Cell	Allow	Criteria
B3:B7	List	=$J$3:$J$4

This is what's in the individual sheets:

SUM SHEETS IF YES1.xlsm
	A	B	C	D	E	F	G	H	I
1					Summary of progress
2						Test Case Results
3					Untested	39	91%	1 h. 46 m.
4					Passed	1	2%	1 h. 00 m.
5					Failed	0	0%	0 h. 00 m.
6					Skipped	3	7%	0 h. 00 m.
7					Observation	0	0%	0 h. 00 m.
8					N/A	0	0%	0 h. 00 m.
9					Total Tested	1	2%	1 h. 00 m.
10					% of test complete		9%
11					Total tasks for Test	43		2 h. 46 m.
12
13
14	TC#	Test Execution Steps	Expected Result	Test Result	Date Tested	Tester	TC Time	Comment / (or Requirement xref)
15	User Story -
16	1	Configure Panel/System as per "Basic Test Set Up"	No expected outcome	P			60 m
17	2	Wire 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 outcome	U			10 m
18	3	Power the panel/system up and enter engineering mode	Panel powers up and engineering mode sucessfully entered	U			2 m
Blocking Element (BE)

Cells with Data Validation
Cell	Allow	Criteria
D16:D18	List	U,P,F,O,S,n/a
A14:B14	Any value
D14	Any 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]

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]

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]

Test Results.xlsm
	A	B	C
1	Date	Sy	Result
2	18-07-21	A	12
3	18-07-21	B	14
4	18-07-21	C	16
5	18-07-21	D	18
6	18-07-21	E	20
7
Test1

Test Results.xlsm
	A	B	C
1	Date	Sy	Result
2	18-07-21	A	13.2
3	18-07-21	B	15.4
4	18-07-21	C	17.6
5	18-07-21	D	19.8
6	18-07-21	E	22
7
Test2

Test Results.xlsm
	A	B	C
1	Date	Sy	Result
2	18-07-21	A	132
3	18-07-21	B	154
4	18-07-21	C	176
5	18-07-21	D	198
6	18-07-21	E	220
7
Test3

 

[Dave Patton]

Test Results.xlsm
	A	B	C
1		Sy	Result
2		A	25.2
3		B	29.4
4		C	33.6
5		D	37.8
6		E	42
7
View1

Cell Formulas
Range	Formula
C2:C6	C2	=SUM(Start:End!C2)

 

[Dave Patton]

Test Results.xlsm
	A	B	C
1		Sy	Total
2		A	25.2
3		B	29.4
4		C	33.6
5		D	37.8
6		E	42
7
View2

Cell Formulas
Range	Formula
C2:C6	C2	=SUMPRODUCT(N(INDIRECT("'"&$G$2:$G$3&"'!"&CELL("address",C2))))

 

[Dave Patton]

Test Results.xlsm
	A	B	C
1		Sy	Total
2		A	157.2
3		B	183.4
4		C	209.6
5		D	235.8
6		E	262.0
7
View3

Cell Formulas
Range	Formula
C2:C5	C2	=SUMPRODUCT(N(INDIRECT("'"&Sheets&"'!"&CELL("address",C2))))
C6	C6	=SUMPRODUCT(N(INDIRECT("'"&Sheets2&"'!"&CELL("address",C6))))

Named Ranges
Name	Refers To	Cells
Sheets	=View3!$G$2:$G$4	C2:C5