Excel Formula help

foxystoat69

Hi there guys, using Excel 2010 here....

http://i130.photobucket.com/albums/p275/foxystoat/Excel.jpg

If I had varying amounts of data (see picture in above link - tried to paste image direct to this page but website wouldn't let me) and wanted to put in sub totals of each group of data (as per the red boxes), what way can I best do this? Obviously, I can't copy the formula and paste all the way down my very lengthy spreadsheet.

Any quick fixes to sort this out?

Cheers

Dave_C_2

Sorry but your link doesn't work:(

Dave

foxystoat69

Dave_C wrote: »

Sorry but your link doesn't work:(

Dave

Ach! Thanks Dave

Link should read http://i130.photobucket.com/albums/p275/foxystoat/Excel.jpg

HoofeHearted

Use Excel Help and read up on "Pivot Tables".

apcorbett

or use:

=SUBTOTAL(9,A1 : A3) NB Remove the spaces between A1 and : and A3 to make it work)

will give you the SUM of the cells shown (A1 to A3) - simply substitute the cells you really want, and Bob is indeed your uncle!

:money:

Stompa

There's probably a better solution, but one way would be as follows: [which assumes your numbers are in column A starting at A1, and requires the addition of a 'helper' column (B)]

Remove the spaces arround the ':'

Into cell B1 put

=COUNTBLANK(A$1 : A1)

copy this value into the rest of column B.

In the cells where you want the first sum to appear in column C put:

=SUMIF(B:B,B1,A : A)

(adjust B1 to be one row above the cell you're using in column C)

Now just copy and paste this into the rest of the cells in column C where you want the sum to appear.

foxystoat69

Thanks, have been shown another way. Works best for me

Column A : Insert your figures complete with gaps
Column B : =IF(A2>0,1,0)
Column C : =IF(B2=1,A2+C1,0)
Column D : =IF(A2="",C1,"")

Hide Columns B & C and copy formulae down.

Stompa

Actually thinking about it, you can do it without needing any extra columns:

Column A : Insert your figures complete with gaps

In B2 put =IF(ISBLANK(A2),SUM(A$1 : A2)-SUM(B$1:B1),"")
and copy formulae down.

ScratchandSniff-->[X]

foxystoat69 wrote: »

wanted to put in sub totals of each group of data (as per the red boxes), what way can I best do this?
Cheers

Sort on the columns you want to group on so all the value for each group come together, then do Data / Subtotal. It will do it at each change in the sorted columns all the way down and also put group & Outline on so you can precis your data.

This still uses the subtotal function but it puts them in for you.