SUBTOTAL(9,RANGE) Function with SUMIF statements

[janema]

Is there a way to use the Subtotal function with SUMIF statements? I want the file to show the total of data when it is filtered, but cannot seem to add in the Subtotal function into the current formula. Perhaps it's not possible.

The current formula is: =(SUMIFS(O:O,T:T,"Yes")*0.035)-SUMIFS(X:X,T:T,"Yes")

So, when the file is filtered, I want it to show the sum of column O if column T equals "Yes" and then subtract the sum of column X if column T equals "Yes"
Right now this pulls the SUM of the column, however, I want them to show as a SUBTOTAL so when I filter it by a leader, it shows the total for what is showing rather than the sum total of the entire file.

I hope that makes sense. Here is what the file looks like:

Thank you soooo much in advance for any guidance and help you can give me to figure this out. I've been trying out formulas for the past hour.

 

[Eric W]

Consider:

Book1
	A														O					T				X	Y	Z	AA
1	Leader														Salary					Merit Eligibility				New Salary		Current	Filter
2	Amy														1000					Yes				1100		-100110	-100110
3	Bob														2000					Yes				2200
4	Amy														3000					Yes				3300
5	Bob														4000					Yes				4400
6	Cal														5000					No				5500
7	Diane														6000					Yes				6600
8	Amy														7000					No				7700
9	Amy														8000					Yes				8800
10	Bob														9000					Yes				9900
11	Bob														10000					Yes				11000
12	Cal														11000					Yes				12100
13	Diane														12000					Yes				13200
14	Amy														13000					Yes				14300
15	Bob														14000					No				15400
16	Amy														15000					Yes				16500
17
18
19
20
Sheet3

Cell Formulas
Range	Formula
Z2	Z2	=(SUMIFS(O:O,T:T,"Yes")*0.035)-SUMIFS(X:X,T:T,"Yes")
AA2	AA2	=SUMPRODUCT(O2:O101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))*0.035-SUMPRODUCT(X2:X101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))

And when it's filtered:

Book1
	A														O					T				X	Y	Z	AA
1	Leader														Salary					Merit Eligibility				New Salary		Current	Filter
2	Amy														1000					Yes				1100		-100110	-42600
4	Amy														3000					Yes				3300
8	Amy														7000					No				7700
9	Amy														8000					Yes				8800
14	Amy														13000					Yes				14300
16	Amy														15000					Yes				16500
17
18
19
20
Sheet3

Cell Formulas
Range	Formula
Z2	Z2	=(SUMIFS(O:O,T:T,"Yes")*0.035)-SUMIFS(X:X,T:T,"Yes")
AA2	AA2	=SUMPRODUCT(O2:O101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))*0.035-SUMPRODUCT(X2:X101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))

Note that the SUMPRODUCT formula does not use whole column references. SUMIFS is smart enough to know what the bottom used row is, SUMPRODUCT is not. So use a range that encompasses all of your data, and add 10% to allow for expansion. Make sure that all the ranges are the same size.

Also note that I used SEQUENCE in the formula, which is available in 365, and 2021 and newer versions of the perpetual licenses. If you need this formula in older versions of Excel, we can substitute the old ROW(INDIRECT("1:100"))-1 construct.

 

[Peter_SSs]

A simplified version of that in AB2

25 01 23.xlsm
	A														O					T				X	Y	Z	AA	AB
1	Leader														Salary					Merit Eligibility				New Salary		Current	Filter	Filter 2
2	Amy														1000					Yes				1100		-100110	-100110	-100110
3	Bob														2000					Yes				2200
4	Amy														3000					Yes				3300
5	Bob														4000					Yes				4400
6	Cal														5000					No				5500
7	Diane														6000					Yes				6600
8	Amy														7000					No				7700
9	Amy														8000					Yes				8800
10	Bob														9000					Yes				9900
11	Bob														10000					Yes				11000
12	Cal														11000					Yes				12100
13	Diane														12000					Yes				13200
14	Amy														13000					Yes				14300
15	Bob														14000					No				15400
16	Amy														15000					Yes				16500
17
SUBTOTAL

Cell Formulas
Range	Formula
Z2	Z2	=(SUMIFS(O:O,T:T,"Yes")*0.035)-SUMIFS(X:X,T:T,"Yes")
AA2	AA2	=SUMPRODUCT(O2:O101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))*0.035-SUMPRODUCT(X2:X101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))
AB2	AB2	=SUM((O2:O101*0.035-X2:X101)*(T2:T101="Yes")*SUBTOTAL(103,OFFSET(A1,SEQUENCE(100),0,1,1)))

And when filtered:

25 01 23.xlsm
	A														O					T				X	Y	Z	AA	AB
1	Leader														Salary					Merit Eligibility				New Salary		Current	Filter	Filter 2
2	Amy														1000					Yes				1100		-100110	-42600	-42600
4	Amy														3000					Yes				3300
8	Amy														7000					No				7700
9	Amy														8000					Yes				8800
14	Amy														13000					Yes				14300
16	Amy														15000					Yes				16500
17
SUBTOTAL

Cell Formulas
Range	Formula
Z2	Z2	=(SUMIFS(O:O,T:T,"Yes")*0.035)-SUMIFS(X:X,T:T,"Yes")
AA2	AA2	=SUMPRODUCT(O2:O101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))*0.035-SUMPRODUCT(X2:X101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))
AB2	AB2	=SUM((O2:O101*0.035-X2:X101)*(T2:T101="Yes")*SUBTOTAL(103,OFFSET(A1,SEQUENCE(100),0,1,1)))

 

[janema]

Consider:

Book1
	A														O					T				X	Y	Z	AA
1	Leader														Salary					Merit Eligibility				New Salary		Current	Filter
2	Amy														1000					Yes				1100		-100110	-100110
3	Bob														2000					Yes				2200
4	Amy														3000					Yes				3300
5	Bob														4000					Yes				4400
6	Cal														5000					No				5500
7	Diane														6000					Yes				6600
8	Amy														7000					No				7700
9	Amy														8000					Yes				8800
10	Bob														9000					Yes				9900
11	Bob														10000					Yes				11000
12	Cal														11000					Yes				12100
13	Diane														12000					Yes				13200
14	Amy														13000					Yes				14300
15	Bob														14000					No				15400
16	Amy														15000					Yes				16500
17
18
19
20
Sheet3

Cell Formulas
Range	Formula
Z2	Z2	=(SUMIFS(O:O,T:T,"Yes")*0.035)-SUMIFS(X:X,T:T,"Yes")
AA2	AA2	=SUMPRODUCT(O2:O101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))*0.035-SUMPRODUCT(X2:X101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))

And when it's filtered:

Book1
	A														O					T				X	Y	Z	AA
1	Leader														Salary					Merit Eligibility				New Salary		Current	Filter
2	Amy														1000					Yes				1100		-100110	-42600
4	Amy														3000					Yes				3300
8	Amy														7000					No				7700
9	Amy														8000					Yes				8800
14	Amy														13000					Yes				14300
16	Amy														15000					Yes				16500
17
18
19
20
Sheet3

Cell Formulas
Range	Formula
Z2	Z2	=(SUMIFS(O:O,T:T,"Yes")*0.035)-SUMIFS(X:X,T:T,"Yes")
AA2	AA2	=SUMPRODUCT(O2:O101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))*0.035-SUMPRODUCT(X2:X101,--(T2:T101="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))

Note that the SUMPRODUCT formula does not use whole column references. SUMIFS is smart enough to know what the bottom used row is, SUMPRODUCT is not. So use a range that encompasses all of your data, and add 10% to allow for expansion. Make sure that all the ranges are the same size.

Also note that I used SEQUENCE in the formula, which is available in 365, and 2021 and newer versions of the perpetual licenses. If you need this formula in older versions of Excel, we can substitute the old ROW(INDIRECT("1:100"))-1 construct.

 

[janema]

I tried the formula but got an error result. I tried both versions and no luck. Am I writing it wrong?

=SUMPRODUCT(O2:O600,--(T2:T600="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))*0.035-SUMPRODUCT(X2:X600,--(T2:T600="Yes"),SUBTOTAL(103,OFFSET(T2,SEQUENCE(100,,0),0,1,1)))

=SUMPRODUCT(O2:O600,--(T2:T600="Yes"),SUBTOTAL(103,OFFSET(T2,ROW(INDIRECT("1:100"))-1)))*0.035-SUMPRODUCT(X2:X600,--(T2:T600="Yes"),SUBTOTAL(103,OFFSET(T2,ROW(INDIRECT("1:100"))-1)))

 

[Eric W]

The ranges aren't the same size. O2:O600 and T2:T600 are the same size (599 cells), but the OFFSET(T2,SEQUENCE(100,,0),0,1,1)) range is only 100 cells. The size is determined by the 100 value in the SEQUENCE function. So try changing the 100 to 599. Same with the ROW(INDIRECT( version.

 

[janema]

The ranges aren't the same size. O2:O600 and T2:T600 are the same size (599 cells), but the OFFSET(T2,SEQUENCE(100,,0),0,1,1)) range is only 100 cells. The size is determined by the 100 value in the SEQUENCE function. So try changing the 100 to 599. Same with the ROW(INDIRECT( version.

Thank you so much! Does the 103 stay as 103 in the SUBTOTAL formula?

SUBTOTAL(103,OFFSET(T2,SEQUENCE(599,,0),0,1,1)))

 

[janema]

The ranges aren't the same size. O2:O600 and T2:T600 are the same size (599 cells), but the OFFSET(T2,SEQUENCE(100,,0),0,1,1)) range is only 100 cells. The size is determined by the 100 value in the SEQUENCE function. So try changing the 100 to 599. Same with the ROW(INDIRECT( versio

It worked!! You are AMAZING! Thank you so much.

 

[Peter_SSs]

I tried both versions and no luck.

It seems that you tried both versions of the SUMPRODUCT suggestion. Did you try the much shorter SUM option from post 5?

Also, if you are marking a solution post, please mark the post that actually contains the solution to help future readers. Post 6 does not contain a solution to the original problem so I have removed the solution mark from that post.

 

[janema]

It seems that you tried both versions of the SUMPRODUCT suggestion. Did you try the much shorter SUM option from post 5?

Also, if you are marking a solution post, please mark the post that actually contains the solution to help future readers. Post 6 does not contain a solution to the original problem so I have removed the solution mark from that post.

Thanks. His original solution worked, I just had to fix a part of the formula and his response contained that solution. I updated and marked the original as the solution.