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

 

[Domenic]

First define the following name (Ribbon >> Formulas >> Defined Names >> Define Name) . . .

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

=SUMPRODUCT(N(INDIRECT("'"&MyArr&"'!A1")))

Hope this helps!

 

[xoScarecrowox]

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]

Sure, we can amend the formula as follows...

=IF(COUNTIF($B$2:$B$10,"Yes")>0,SUMPRODUCT(N(INDIRECT("'"&MyArr&"'!A1"))),0)

 

[xoScarecrowox]

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

 

[Domenic]

You're very welcome, glad to help.

Cheers!

 

[xoScarecrowox]

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]

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
	A	B	C	D	E
1	Sheets	YES	45	45	Total
2	1a	YES		10	By Sheet
3	1b	YES		0	By Sheet
4	1c	YES		15	By Sheet
5	1d	YES		0	By Sheet
6	1e	YES		20	By Sheet
Summary

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"B1"),B1,INDIRECT("'"&Sheets&"'!"&"A1")))
D1	D1	=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$6&"'!"&"B1"),B1,INDIRECT("'"&$A$2:$A$6&"'!"&"A1")))
D2:D6	D2	=SUMPRODUCT(SUMIF(INDIRECT("'"&$A2&"'!"&"B1"),B2,INDIRECT("'"&$A2&"'!"&"A1")))

Named Ranges
Name	Refers To	Cells
Sheets	=Summary!$A$2:$A$6	C1:D1, D2

 

[Dave Patton]

With 10 in A1 for each sheet and just using Sumif

3D Sumif.xlsm
	A	B	C	D
1	Sheets	YES	30	Total
2	1a	YES	10	By Sheet
3	1b	YES	0	By Sheet
4	1c	YES	10	By Sheet
5	1d	YES	0	By Sheet
6	1e	YES	10	By Sheet
7
Summary

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$6&"'!"&"B1"),B1,INDIRECT("'"&$A$2:$A$6&"'!"&"A1")))
C2:C6	C2	=SUMIF(INDIRECT("'"&$A2&"'!"&"B1"),B2,INDIRECT("'"&$A2&"'!"&"A1"))

Named Ranges
Name	Refers To	Cells
Sheets	=Summary!$A$2:$A$6	C1:C2

 

[xoScarecrowox]

With 10 in A1 for each sheet and just using Sumif

3D Sumif.xlsm
	A	B	C	D
1	Sheets	YES	30	Total
2	1a	YES	10	By Sheet
3	1b	YES	0	By Sheet
4	1c	YES	10	By Sheet
5	1d	YES	0	By Sheet
6	1e	YES	10	By Sheet
7
Summary

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$6&"'!"&"B1"),B1,INDIRECT("'"&$A$2:$A$6&"'!"&"A1")))
C2:C6	C2	=SUMIF(INDIRECT("'"&$A2&"'!"&"B1"),B2,INDIRECT("'"&$A2&"'!"&"A1"))

Named Ranges
Name	Refers To	Cells
Sheets	=Summary!$A$2:$A$6	C1: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;

Sheets	YES	0	0​
1a	YES		0​
1b	YES		0​
1c	YES		0​
1d	YES		0​
1e	YES		0​

Cell Formulas
Range	Cell	Formula
C1	C1	=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"B1"),B1,INDIRECT("'"&Sheets&"'!"&"A1")))
D1	D1	=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$6&"'!"&"B1"),B1,INDIRECT("'"&$A$2:$A$6&"'!"&"A1")))
D2:D6	D2	=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"))