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
 
Sheet NamesInclude Yes/NoValue of A1 in each sheet, I don't want this on main summary sheet thoughSum of values from A1 of each sheet where B is option Yes
1aYES3010Yes selected 'Include'
1bNo10No Selected 'Don't include'
1cYES10Yes selected 'Include'
1dYES10Yes selected 'Include'
1eNo10No Selected 'Don't include'

My 'Summary' sheet is set like this;
Sheet NamesInclude Yes/NoValue of A1 in each sheet, I don't want this on main summary sheet though
1aYES30
1bNo
1cYES
1dYES
1eNo

The sheetnames are dynamic and will vary in the amount.
I want a formula that will scan through column B and where there is Yes, sum the value of those sheets to give me a total in cell C2.
Its simple when in the same sheet with a SUMIF function if column A was a number - =SUMIF(B2:B6,"YES",D2:D6)
 

Attachments

  • 1626708168778.png
    1626708168778.png
    7.3 KB · Views: 4
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
We need a clear explanation of what is on the data sheets and what you wish to calculate.
I provided formulas that work but I really do not know what you are trying to achieve.

We cannot see your spreadsheet and we do not know what you need.
My suggestion did not require Named Ranges; you stated that your users could not use Named Ranges.

You can post an extract of your sheets with the forum's tool XL2BB.
 
Upvote 0
SUM SHEETS IF YES.xlsm
E
13
Summary


As you will see from my formula, the results are populated down column 'C' but I only want the sum of that result in 'C2', and also it seems to be adding where column 'B' is a 'NO' where I only want the sum of values from A1 across the sheets where YES is in column 'B'. For the example it should be 30, the sum of sheets 1a,1c and 1e.

Hope that makes sense :)
 
Last edited:
Upvote 0
You didn't provide any information.

3D Sumif.xlsm
ABC
1Yes30
Summary
Cell Formulas
RangeFormula
C1C1=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$6&"'!"&"B1"),B1,INDIRECT("'"&$A$2:$A$6&"'!"&"A1")))
 
Upvote 0
SUM SHEETS IF YES1.xlsm
ABCDEFGH
1Summary sheet dataData in each sheet
2TestY/NTest Case Results
3Blocking Element (BE)YESU3991%1 h. 46 m.
4wAppLoxxNOP12%1 h. 00 m.
5CamerasYESF00%0 h. 00 m.
6RepeaterYESS37%0 h. 00 m.
7ARC RedundancyNOO00%0 h. 00 m.
8Keypad LED IndicationNON/A00%0 h. 00 m.
9GDPR + OSSYESTotal Tested12%1 h. 00 m.
10Special Keypad (Log Delete)NO% of test complete9%
11etc,etc,etc,,,,,,NO,NO,NO,,,,,Total tasks for Test432 h. 46 m.
12
13This is a SUM of the sheets where YES is selected only from table above.
14USUM OF "F3"(SHEET(A3+A5+A6+A9)
15PSUM OF "F4"(SHEET(A3+A5+A6+A9)
16FSUM OF "F5"(SHEET(A3+A5+A6+A9)
17SSUM OF "F6"(SHEET(A3+A5+A6+A9)
18OSUM OF "F7"(SHEET(A3+A5+A6+A9)
19N/ASUM OF "F8"(SHEET(A3+A5+A6+A9)
Summary



Is this any better?
 
Upvote 0
A2:A9 is your list of sheets
Data sheets cell B3 is Yes or No
Data sheets cell F3 has the required amount
Summary shows total of F3 when B3 is Yes

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$9&"'!"&"B3"),"yes",INDIRECT("'"&$A$2:$A$9&"'!"&"F3")))
 
Upvote 0
A2:A9 is your list of sheets
Data sheets cell B3 is Yes or No
Data sheets cell F3 has the required amount
Summary shows total of F3 when B3 is Yes

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$9&"'!"&"B3"),"yes",INDIRECT("'"&$A$2:$A$9&"'!"&"F3")))
For the sample I sent: some amendments to your comments;
A2:A9 is your list of sheets --> Its A3:A10 in my sample, A2 is the title of column, could be more or less amount of sheets, A3:A
Data sheets cell B3 is Yes or No --> B3:B10 in 'Summary' sheets is YES or NO
Data sheets cell F3 has the required amount --> Correct, all sheets same cell reference.
Summary shows total of F3 when B3 is Yes --> It should SUM the number from each sheet where YES is selected in column B
from sample above:
Blocking Element (BE), Cameras, Repeater & GDPR + OSS are all set to yes so the SUM should be from those sheets only, 39 in F3 of each sheet therefore SUM would be 4 x 39
 
Upvote 0
SUM SHEETS IF YES1.xlsm
ABCDEFGHIJK
1Summary sheet dataData in each sheet
2TestYES/NOSUM of sheets F3 with YES in column BTest Case ResultsYES
3Blocking Element (BE)YES156U3991%1 h. 46 m.NO
4wAppLoxxNOP12%1 h. 00 m.
5CamerasNOF00%0 h. 00 m.
6RepeaterYESS37%0 h. 00 m.
7ARC RedundancyYESO00%0 h. 00 m.
8Keypad LED IndicationNON/A00%0 h. 00 m.
9GDPR + OSSYESTotal Tested12%1 h. 00 m.
10Special Keypad (Log Delete)NO% of test complete9%
11etc,etc,etc,,,,,,NO,NO,NO,,,,,Total tasks for Test432 h. 46 m.
Summary
Cell Formulas
RangeFormula
C3C3=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$2:$K$3


Cell C2 will show the formula which better explains what I need, the problem is its long and is ok for a small number of sheets but potentially I could have 50+ sheets which would make this formula quite lengthy so I was wondering if there was a shorter one to achieve the same result, unfortunately I can't use named ranges or a macro or I could do it with the original answer to my request.

I have my fingers crossed :)
 
Upvote 0
"I could do it with the original answer to my request" The formula suggested works

Try the formula posted in # 16. =SUMPRODUCT(SUMIF(INDIRECT("'"&$A$3:$A$10&"'!"&"B3"),"yes",INDIRECT("'"&$A$3:$A$10&"'!"&"F3")))

=IF(B3="Yes",SUMPRODUCT(SUMIF(INDIRECT("'"&$A$3:$A$10&"'!"&"B3"),"yes",INDIRECT("'"&$A$3:$A$10&"'!"&"F3"))),"")
 
Last edited:
Upvote 0
"I could do it with the original answer to my request" The formula suggested works

Try the formula posted in # 16. =SUMPRODUCT(SUMIF(INDIRECT("'"&$A$3:$A$10&"'!"&"B3"),"yes",INDIRECT("'"&$A$3:$A$10&"'!"&"F3")))

=IF(B3="Yes",SUMPRODUCT(SUMIF(INDIRECT("'"&$A$3:$A$10&"'!"&"B3"),"yes",INDIRECT("'"&$A$3:$A$10&"'!"&"F3"))),"")
SUM SHEETS IF YES1.xlsm
ABCDEFGHIJK
1TestYES/NOSUM of sheets F3 with YES in column BData in each sheet
2Blocking Element (BE)YES0Test Case ResultsYES
3wAppLoxxYES234U3991%1 h. 46 m.NO
4CamerasNOP12%1 h. 00 m.
5RepeaterNOF00%0 h. 00 m.
6ARC RedundancyYESS37%0 h. 00 m.
7Keypad LED IndicationYESO00%0 h. 00 m.
8GDPR + OSSYESN/A00%0 h. 00 m.
9Special Keypad (Log Delete)YESTotal Tested12%1 h. 00 m.
10etc,etc,etc,,,,,,NO,NO,NO,,,,,% of test complete9%
11Total tasks for Test432 h. 46 m.
Summary
Cell Formulas
RangeFormula
C2C2=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$9&"'!"&"B3"),"YES",INDIRECT("'"&$A$2:$A$9&"'!"&"F3")))
C3C3=IF(B2="YES",INDIRECT("'"&A$2&"'!"&"F3"))+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"))
Cells with Data Validation
CellAllowCriteria
B2:B10List=$K$2:$K$3


It does not work for me, I have put your formula in C2 and the result is 0, I placed my formula in C3 so you can see the expected result.

Your formula only seems to consider the state of B2 to perform the function where it needs to calculate the result with consideration to C2:C9
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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
Back
Top