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
 

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
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 2, 2019
Messages
15
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
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 2, 2019
Messages
15

ADVERTISEMENT

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

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
You're very welcome, glad to help.

Cheers!
 

xoScarecrowox

New Member
Joined
Apr 2, 2019
Messages
15

ADVERTISEMENT

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)

Thanks in advance
 

Dave Patton

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

SheetsYES0
0​
1aYES
0​
1bYES
0​
1cYES
0​
1dYES
0​
1eYES
0​

Cell Formulas
RangeCellFormula
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=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
    Capture.JPG
    18.4 KB · Views: 3

Forum statistics

Threads
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.
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