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.

 

[Peter_SSs]

I updated and marked the original as the solution.

Thanks for that.

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

Cheers, I understand that - though if you don't try things that have been suggested, it doesn't encourage those people to spend time to help you next time.

 

[janema]

Thanks for that.



Cheers, I understand that - though if you don't try things that have been suggested, it doesn't encourage those people to spend time to help you next time.

Thank you. Very true. I did try every suggestion but seemed to have to go back to the original. I'm sorry, I am not as savvy and amazing at excel as all of you. I appreciate you guys so incredibly much! I have goals to be as good in excel as you guys are!

 

[Peter_SSs]

I did try every suggestion but seemed to have to go back to the original.

Does that mean the SUM formula I suggested did not work for you?
The adjustment for your latest ranges would be

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

 

[janema]

Does that mean the SUM formula I suggested did not work foe you?
The adjustment for your latest ranges would be

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

For some reason, it gave me a value error. It could very likely be that I missed something with the numbers because I highly doubt your formula was incorrect. I just don't think I understood how to properly write it for my range of data.

 

[Peter_SSs]

I just don't think I understood how to properly write it for my range of data.

So, are you changing the formula from post 13 in some way?

To satisfy my curiosity could you try this for me?

1. Start a blank worksheet
   
   
2. Click this icon
   
   at the top left of my first mini sheet in post 3
   
   
3. Select A1 in your blank worksheet
   
   
4. Paste

Does it produce -100110 in cell AB2?
If not, what does appear in AB2?

If you filter column A on Amy, does it return -42600 in AB2?
If not, what does it return?

 

[Peter_SSs]

Could you also please try this formula with your data?

=SUM(IFERROR((O2:O600*0.035-X2:X600)*(T2:T600="Yes"),0)*SUBTOTAL(103,OFFSET(A1,SEQUENCE(599),0,1,1)))

 

[shaowu459]

almost the same with Eric's solution, for your reference:

工作簿1.xlsx
	A														O					T				X	Y	Z	AA	AB
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
Sheet1

Cell Formulas
Range	Formula
Z2	Z2	=(SUMIFS(O:O,T:T,"Yes")*0.035)-SUMIFS(X:X,T:T,"Yes")
AA2	AA2	=SUM((O2:O301*0.035-X2:X301)*(T2:T301="Yes")*SUBTOTAL(103,OFFSET(O1,SEQUENCE(300),)))

 

[Peter_SSs]

almost the same with Eric's solution, for your reference:

Not sure what you are saying/suggesting. Is that really any different to post 3?

 

[shaowu459]

Not sure what you are saying/suggesting. Is that really any different to post 3?

Oh, sorry. i didn't view all posts in this thread and didn't see post 3. when i opened this thread, i just saw the answer marked as solution which is on the top of page. then copied the minisheet to my own workbook and write formula by myself. This question is not a complicate one, so it is very probably different people get the same answer.

 

[janema]

Could you also please try this formula with your data?

=SUM(IFERROR((O2:O600*0.035-X2:X600)*(T2:T600="Yes"),0)*SUBTOTAL(103,OFFSET(A1,SEQUENCE(599),0,1,1)))

Sure, I will try the ones you asked me to when I'm back at my computer later today. Sorry, I feel asleep last night. Lol