Conditional Subtotal

[Fleeeee]

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

 

[dermie_72]

Does it need to be a subtotal or will a sumifs work for you?

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

 

[Fleeeee]

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!

 

[Fleeeee]

Does it need to be a subtotal or will a sumifs work for you?

=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!!!

 

[Fleeeee]

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

 

[dermie_72]

you could use subtotal(109,Range). This will adjust to whatever is visible at the time.

 

[Fleeeee]

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!

 

[dermie_72]

you could change the sumifs to refernce the criteria you are filtering by. Sumifs(Sum Range, Criteria Range, Criteria).

 

[Fleeeee]

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!