Conditional Subtotal

Fleeeee

New Member
Joined
Feb 19, 2025
Messages
6
Office Version
  1. 2024
Hi all, first time posting hopefully this is the right place to ask.
If anyone can help me please, I'm trying to use a subtotal formula that calculates values in one column based on the text in another column.
Below image is what I'm trying to achieve. If I filter on Colum A for "A" it gives me the total then in in Cell B12 where I'd have the formula.
Hopefully I've made sense and really appreciate any help. Love this Forum!! :)
 

Attachments

  • Subtotal Grab.gif
    Subtotal Grab.gif
    9.2 KB · Views: 13
Does it need to be a subtotal or will a sumifs work for you?
Excel Formula:
=sumifs(B$2:B$6,A$2:A$6,B10)
in B12 and copy it down to B17
If the 0-30 is relevant, then you can add another criteria to this formula.
Hope this was what you were looking for.
 
Upvote 0
Thanks for your quick response!
I applied you formula but what I'm trying to achieve is if I filter column A to "B" it sums up the values of "B" in Cell B17. I'll repeat the same formula for "A" then separately in Cell B12.

thanks again really appreciate your help! :)
 
Upvote 0
Does it need to be a subtotal or will a sumifs work for you?
Excel Formula:
=sumifs(B$2:B$6,A$2:A$6,B10)
in B12 and copy it down to B17
If the 0-30 is relevant, then you can add another criteria to this formula.
Hope this was what you were looking for.
Ignore my last response I was having a "Duh" moment it's all good I was able to apply your Formula perfectly thanks very much, legend!!! :)
 
Upvote 0
Hi again, sorry to trouble you again! I've applied your formula however when I filter (Eg in Column L) the values stay the same in rows 18 and 24. I was hoping that the values would recalulate with whatever filters are used. Is there something I'm doing wrong or is it maybe a different formula I need?
I did try to mess with yours but still couldn't figure it out so it wasn't for the lack of trying :|
Please see below screenshot for some illustration.
Thanks so much in advance!!
 

Attachments

  • Subtotal Grab II.gif
    Subtotal Grab II.gif
    40.7 KB · Views: 5
Upvote 0
Thanks for your suggestion however I need to show the results for A and B at any given time. If I use the Subtotal 109 then the results will be the same in the table for A & B.

What I need a formula in Cell T18 and T24 giving the sum dependent on Column A.

Any help is greatly appreciated, thanks!
 
Upvote 0
you could change the sumifs to refernce the criteria you are filtering by. Sumifs(Sum Range, Criteria Range, Criteria).
 
Upvote 0
I thought I was half-decent at excel and sorry again to be on about this when I used what I took from your suggestion it's returning a value error. Image below is what I thought you meant.
Is there any way you could type out the formula of how it should look or point me out where I'm being a dumb ***?
thanks again!
 

Attachments

  • Subtotal Grab III.gif
    Subtotal Grab III.gif
    51.2 KB · Views: 0
Upvote 0

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
Back
Top