LET/VSTACK/IF/SUM/FILTER

[Jedi Master]

I have 10 identical worksheets that look like the below:

The following formula in cell A78 works perfectly to add all values together:
=IF($D78="","",LET(X,VSTACK('Change Order Worksheet'!$A$78:$D$113,'Change Order Worksheet (2)'!$A$78:$D$113,'Change Order Worksheet (3)'!$A$78:$D$113,'Change Order Worksheet (4)'!$A$78:$D$113,'Change Order Worksheet (5)'!$A$78:$D$113,'Change Order Worksheet (6)'!$A$78:$D$113,'Change Order Worksheet (7)'!$A$78:$D$113,'Change Order Worksheet (8)'!$A$78:$D$113,'Change Order Worksheet (9)'!$A$78:$D$113,'Change Order Worksheet (10)'!$A$78:$D$113),SUM(FILTER(CHOOSECOLS(X,1), CHOOSECOLS(X,4)=$D78))))


Named ranges: p_1 through p_10 refer to cell P13 in each of the 10 worksheets with a "Yes/No" value. I need to add the IF statement to only sum values IF P13 on each sheet is marked "Yes". I am trying the following formula and receiving an #N/A error:

=IF($D78="","",LET(x,VSTACK(IF(p_1="Yes",'Change Order Worksheet'!$A$78:$D$113,""),IF(p_2="Yes",Change Order Worksheet (2)'!$A$78:$D$113,""),IF(p_3="Yes",Change Order Worksheet (3)'!$A$78:$D$113,""),IF(p_4="Yes",Change Order Worksheet (4)'!$A$78:$D$113,""),IF(p_5="Yes",Change Order Worksheet (5)'!$A$78:$D$113,""),IF(p_6="Yes",Change Order Worksheet (6)'!$A$78:$D$113,""),IF(p_7="Yes",Change Order Worksheet (7)'!$A$78:$D$113,""),IF(p_8="Yes",Change Order Worksheet (8)'!$A$78:$D$113,""),IF(p_9="Yes",Change Order Worksheet (9)'!$A$78:$D$113,""),IF(p_10="Yes",Change Order Worksheet (10)'!$A$78:$D$113,"")),SUM(FILTER(CHOOSECOLS(X,1), CHOOSECOLS(X,4)=$D78))))

My formula is definately flawed. Can anyone help me add the IF statement to the working formula above please?

 

[Eric W]

Two options:

Book3
	A	B	C	D	E	F
76	MATERIALS
77	Quantity	Units	Unit Price	Material
78	6	SY	$ 1.43	Geotextile (Non-Woven #6)
79	24	ton(s)	$ 32.00	Stone - 304 Limestone
80
Summary

Cell Formulas
Range	Formula
A78	A78	=SUM(IF(p_1="Yes",SUMIFS('Change Order Worksheet (1)'!$A$78:$A$100,'Change Order Worksheet (1)'!$D$78:$D$100,D78)),IF(p_2="Yes",SUMIFS('Change Order Worksheet (2)'!$A$78:$A$100,'Change Order Worksheet (2)'!$D$78:$D$100,D78)),IF(p_3="Yes",SUMIFS('Change Order Worksheet (3)'!$A$78:$A$100,'Change Order Worksheet (3)'!$D$78:$D$100,D78)))
A79	A79	=LET(s,SEQUENCE(3),ws,"'Change Order Worksheet ("&s&")'!",SUM(COUNTIF(INDIRECT("p_"&s),"Yes")*SUMIFS(INDIRECT(ws&"A78:A100"),INDIRECT(ws&"D78:D100"),D79)))

Named Ranges
Name	Refers To	Cells
p_1	='Change Order Worksheet (1)'!$P$13	A78
p_2	='Change Order Worksheet (2)'!$P$13	A78
p_3	='Change Order Worksheet (3)'!$P$13	A78

The first option in A78 is closest to your original formula. Instead of stacking everything, I just handled the parts separately. Note that I only used 3 worksheets in my example, you'll need to update it to 10.

The second option uses INDIRECT and arrays to shorten the formula considerably. INDIRECT is volatile, and can slow down your sheet if you have a lot of them. You'll also need to rename the "Change Order Worksheet" to "Change Order Worksheet (1)". Other than that, just change the SEQUENCE(3) to SEQUENCE(10), and the range references.

Let us know if one of these works for you.

 

[Jedi Master]

Two options:

Book3
	A	B	C	D	E	F
76	MATERIALS
77	Quantity	Units	Unit Price	Material
78	6	SY	$ 1.43	Geotextile (Non-Woven #6)
79	24	ton(s)	$ 32.00	Stone - 304 Limestone
80
Summary

Cell Formulas
Range	Formula
A78	A78	=SUM(IF(p_1="Yes",SUMIFS('Change Order Worksheet (1)'!$A$78:$A$100,'Change Order Worksheet (1)'!$D$78:$D$100,D78)),IF(p_2="Yes",SUMIFS('Change Order Worksheet (2)'!$A$78:$A$100,'Change Order Worksheet (2)'!$D$78:$D$100,D78)),IF(p_3="Yes",SUMIFS('Change Order Worksheet (3)'!$A$78:$A$100,'Change Order Worksheet (3)'!$D$78:$D$100,D78)))
A79	A79	=LET(s,SEQUENCE(3),ws,"'Change Order Worksheet ("&s&")'!",SUM(COUNTIF(INDIRECT("p_"&s),"Yes")*SUMIFS(INDIRECT(ws&"A78:A100"),INDIRECT(ws&"D78:D100"),D79)))

Named Ranges
Name	Refers To	Cells
p_1	='Change Order Worksheet (1)'!$P$13	A78
p_2	='Change Order Worksheet (2)'!$P$13	A78
p_3	='Change Order Worksheet (3)'!$P$13	A78

The first option in A78 is closest to your original formula. Instead of stacking everything, I just handled the parts separately. Note that I only used 3 worksheets in my example, you'll need to update it to 10.

The second option uses INDIRECT and arrays to shorten the formula considerably. INDIRECT is volatile, and can slow down your sheet if you have a lot of them. You'll also need to rename the "Change Order Worksheet" to "Change Order Worksheet (1)". Other than that, just change the SEQUENCE(3) to SEQUENCE(10), and the range references.

Let us know if one of these works for you.

Thank you so much! The first option works perfectly

 

[Eric W]

Happy to help!