Subtotal

[sqlwp]

I need to do a conditional subtotal on a field. If any cell in column A is the value "R" then sum the associated value in column B. How can I accomplish this with a subtotal? I need to use subtotal because autofiltering can be used on many different columns.

 

[PeterMoran]

Hi sqlwp,

Why not use Column C to contain all those Col B items which have an "R" in Col A, then use a Subtotal on col C.

Good Luck!
Peter Moran

 

[xlbo]

sounds like you need to look into the SUMIF function rather than a subtotal - I get the impression that your "R"s could be anywhere in the column

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[sqlwp]

I could split out the value in column B based on the value in column A and subtotal those columns individually, but this spreadsheet is huge and contains many columns that I have to total based on this condition so I would rather not have to do that.
I have to use the subtotal function because the spreadsheet can be filtered on by many different fields and the totals need to reflect only those records that are being filtered, that's why I can't use the sumif.

 

[GlennUK]

So, why can't you filter on column A being "R"?




Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[sqlwp]

I don't know how to tell the subtotal command to look at every cell in column A for an "R" and sum the associated value in column B. If I use a condition with the subtotal it only looks at one cell:
=IF(A12="R",(SUBTOTAL(9,B12:B45623)),0)
How do I give it a range condition?

 

[bluedragon2]

=SUMPRODUCT((A1:A1000="R")*(B1:B1000))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[GlennUK]

If you filter on column A being "R" then the SUBTOTAL function will only include the displayed rows. Isn't that what you want?



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[sqlwp]

The sumproduct formula doesn't work if autofilter is used on any of the fields in the worksheet, that's why I need to use subtotal.

 

[GlennUK]

Hi sqlwp,

again I ask, why can't you filter on column A?


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[sqlwp]

Because there are other subtotals that need all values for column A.

 

[GlennUK]

In that case PeterMoran gave you the answer near the top of this thread.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[sqlwp]

That would certainly work but I would have to do it for over 10 columns that I'm trying to subtotal. I was looking for a way to do it more dynamically through a formula.
It looks like that might not be possible.

 

[xlbo]

Well - if your process is inflexible then you will always run the risk of having to carry out more manual tasks !

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question