Subtotals / Grand Total

[dp]

This should be so simple but I cant figure out how to get a Grand Total cell
B16 without naming each sub total cell. I used 'auto sum' icon to get a
subtotal on b5, b10, b14. Help lost me on paste functions, math, sum because
I got a sum that included the sub totals as another entry. Obviously I am a
very new Excel user. TIA
Col A Col B
Mr. Smith
January 5.00
February 6.00
March 7.00
18.00

ABC Co
January 15.00
February 20.00
35.00

DEF Co
January 10.00
10.00

Grand Total ?

 

[Ron Coderre]

Try something like this:

B16: =SUMIF(A1:A15,"",B1:B15)

OR....if you have some control over the intermediate totals...
Use the SUBTOTAL function. The SUBTOTAL function ignores other SUBTOTAL
functions in it's referenced range.

Example:
B5: =SUBTOTAL(9,B2:B4)
B10: =SUBTOTAL(9,B7:B9)
B14: =SUBTOTAL(9,B12:B13)

and the grand total would be:
B16: =SUBTOTAL(9,B1:B15)
That formula will ignore cells B5, B10 and B14.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[dp]

Sorry - Excel 2000

 

[Scores]

There's a couple ways you can do this.

First you can, in your "Grand Total" cell type =SUM( then click on each
cell you want it to add while holding the control key. Once you've clicked on
all your sub-totals, press Enter.

Another way is to layout your data differently.

Row 1=Company
Row 2=January
Row 3=February
Row 4=March
Row 5=Sub-total

At the end of your Sub-totals (assuming you'll be in E5, with data in E2
through E4) you can now do the formula =SUM(E2:E4)

You can also lay the data out in Column form as well
Column A=Company
Column B=January
Column C=February
Column D=March
Column E=Sub-total

Since all your sub-totals will be in colum E, you can auto-sum on that.

 

[dp]

Eureka! The SUBTOTAL function worked. Once I figured out that the function I
needed was 9 and selected the B column for ref1 it worked. Thank you.

 

[Roger Govier]

Hi

In addition to using the Subtotal function you could also use
=SUM(B1:B15)/2

 

[Ron Coderre]

I'm glad I could help......and thanks for the feedback.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[dp]

Clever - more surprising - I understand!

Hi

In addition to using the Subtotal function you could also use
=SUM(B1:B15)/2