Excel Subtotal Help

[vbridge]

I'm trying to do use the subtotal function in an Excel spreadsheet. I have 5 worksheets in the file. I got the subtotal function to work perfectly on the first three tabs, but the last two units are far larger and there are too many references for the function to work. Is there another way to do this? Here is a sample of what my information looks like.

A B
1 SUBTOTAL =SUBTOTAL(B2:B5,B7:B9)
2 Thermocouples $$$$
3 SH Tubes $$$$
4 RH Tubes $$$$
5 Waterwall $$$$
6 Boiler =SUM(B2:B5)
7 BFP $$$$
8 BFP Motor $$$$
9 Condensate Pump $$$$
10 Auxiliaries =SUM(B7:B9)

I have another column next to the dollar amounts that automatically populates itself with either a P or an R depending on it's value. If I wanted to filter out just the P's or just the R's, I needed the subtotal field to see what the total is just for those particular line items. However, I have too many references. Can anyone help me? Thanks.

Veronica

 

[xlbo]

Have you thought about using a pivot table. Not sure if the rest of your data is condusive to it but worth looking into as it is a very powerful summarising tool

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

What do you mean you have too many references? Do you mean too many to do by hand? If so then have you tried Data / Subtotals which will do them all automatically.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[vbridge]

I have, in the list, major components, and under the major components are the individual pieces of equipment. Like in my sample, the major component is the boiler and the auxiliaries. Those lines just sum the dollar amounts in that particular category. When I do the subtotal, I do not want to include my summaries. The subtotal function is SUBTOTAL([ref1], [ref2], [ref3]...) My references, or ranges, for the above sample are B2:B5, and B7:B9. In the sample above, that formula would work perfectly. However, in the spreadsheet that I created, there are 50 major components and hundreds of pieces of equipment. Therefore, my formula doesn't work because the subtotal function must only allow a certain amount of references.

 

[GlennUK]

I don't think you understand how SUBTOTAL works ... your example should be done like this:

A B
1 SUBTOTAL =SUBTOTAL(9,B2:B10)
2 Thermocouples $$$$
3 SH Tubes $$$$
4 RH Tubes $$$$
5 Waterwall $$$$
6 Boiler =SUBTOTAL(9,B2:B5)
7 BFP $$$$
8 BFP Motor $$$$
9 Condensate Pump $$$$
10 Auxiliaries =SUBTOTAL(9,B7:B9)

... see that the overall SUBTOTAL references the entire range, and ignores nested SUBTOTALS, so you don't need to specify loads of ranges as you seem to think.

Cheers, Glenn.

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

 

[vbridge]

You're right. I did not know that. It worked perfectly. Thanks for the help.