Sporadic Totals in Excel [video]

Posted on December 18th, 2013 in Excel Howtos - 38 comments

If this Excel problem is a Bollywood (Indian movie) plot, it would go like this:

Situation: Your boss gave you a worksheet. It has a lot of number chunks. And you need to calculate the sum of each chunk. Quickly!

Twist #1: The villain (your boss, who else) has abducted  your spouse. For every extra hour you spend on the problem, your boss will make your spouse go thru one of the boring 97 slide strategy presentations. And his laptop is full of those strategy presentations.

Twist #2: The F1 key on your keyboard is missing.

Twist #3: The coffee machine in your floor is broken again.

Twist #4: And just when you are pressing CTRL+S, the movie steers in to an item song.

—-

Fortunately, no one abducted your spouse. And hopefully the coffee machine is working. But the Excel problem remains unsolved.
Sporadic totals in Excel - example data

Sporadic totals

This problem is based on a call I received last week from one of our readers in UK. He had a worksheet full of numbers with blank rows between every few numbers. And he wants to calculate the totals of individual chunks of numbers quickly. He cannot write one formula and paste it everywhere as the chunks are not uniformly sized. He cannot write individual formulas as the data is very large.

So what to do?

If we are still in a Bollywood film, you can write all the 10,000 formulas and simultaneously sipping screwdrivers & shimmying to a snazzy song with sexy starlets.

Alas, this is not a movie.

But we still manage to look awesome. Thanks to superb sidekicks – Goto Special & Autosum.

Calculating Sporadic Totals in a second

See this short video to understand how to calculate sporadic totals in a few seconds. With the time saved, you could fix yourself a cocktail (or coffee) and hum a beautiful song.

Watch the video on our YouTube Channel or Facebook Page.

Sporadic Totals – Alternative treatment

It is an awesome co-incidence that both MrExcel (Bill Jelen) and Kevin Lehrbass also published videos about this concept around the same time. MrExcel shows how to use VBA to do this, where as Kevin talks about using formulas. Check out both videos too.

Not enough sporadic data? Try this practice file

If you want to practice this technique, use this Excel file.

Leave the drama to movies, Learn Excel

We all love film drama like blowing up cars, high-speed chases, super-human stunts and spicy songs. But you sure don’t want that in your life. So learn Excel. Save time, use that to enjoy the drama elsewhere.

Click here to join our newsletter. Get awesome one step at a time.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

38 Responses to “Sporadic Totals in Excel [video]”

  1. Herman says:

    I noticed that your method doesn’t work when one of the chunks only contain one row. Could you double check on that?

    • Chandoo says:

      You are right. This approach works only when all chunks have more than 1 number.

    • JC says:

      noticed if only one row you could do:
      select the data in col C
      F5 – select blank cells
      autosum
      F5 – select formulas
      F5 – select dependents (direct only)
      hit delete
      F5 – select formulas, then format appropriately

      only problem is Cell C202 will not sum as the select blanks will not select that cell unless something is in cell B203, but knowing that only applies to the last data set, you could just put a character in there

  2. Darin Myers says:

    My preference is almost always to keep the data and the calculations as separate as possible. (I am well aware that bosses can easily and often demand otherwise) To that end I would use one of two approaches to calculate this information.

    The easiest would be to simply create a pivot table and hide the blank item. This wouldn’t sort this data correctly, but you could also easily add further calculations on the same data set.

    The second approach would be to copy the customer column, paste it into another sheet, remove duplicates, and then calculate the totals using SUMIF. [i.e. =SUMIF(‘sporadic totals – practice’!$B$5:$B$197,A2,’sporadic totals – practice’!$C$5:$C$197) ]

  3. Stephen says:

    VBA handles this very nicely:
    [code]
    Sub gettotals()
    Dim toprange
    Dim bottomrange
    Application.ScreenUpdating = False
    ActiveSheet.Range(“C5″).Select

    beginloop:
    toprange = ActiveCell.Address
    If IsEmpty(ActiveCell.Offset(1, 0)) Then GoTo onevalue
    Selection.End(xlDown).Select
    onevalue:
    bottomrange = ActiveCell.Address
    ActiveCell.Offset(1, 1).Select
    ActiveCell.Formula = “=SUM(” & toprange & “:” & bottomrange & “)”
    ActiveCell.Offset(0, -1).Select
    Selection.End(xlDown).Select
    If IsEmpty(ActiveCell) Then Exit Sub
    GoTo beginloop
    Application.ScreenUpdating = True

    End Sub
    [/code]

  4. Ariel says:

    Another fast way to calculate sporadic totals (using the example worksheet with two xollumns) is by using DATA/SUBTOTAL in the menu. In trhis case you have to select both collumns and add subtotals for Sales when Customer changes.

  5. himanshu says:

    We can also use subtotals in excel . that is also simple

  6. Jon Acampora says:

    Great tip! One trick to add… You can get AutoSum to use the SUBTOTAL function instead of the SUM function if you apply a filter before clicking the AutoSum button.

    For example, if you follow the steps in the video with the example workbook. BEFORE you select the constants by using the GoTo>Special menu, select cell C4 and click the Filter button on the Data tab of the Ribbon. In the filter drop-down in C4, uncheck (Blanks) from the criteria list. Now select the range C5:C97 and select constants with the GoTo>Special menu (you can also use keyboard shortcut Alt+; to select visible cells).

    Now press the AutoSum button (Alt+=) and the blank cells below each group of values will be automatically filled with the SUBTOTAL function instead of SUM. You could then use any of the SUBTOTAL functions – Count, Average, etc. to return various results in the sheet.

    I’m not sure of the practical application of this yet, but it does work. :)

  7. Rajib says:

    @Jon Acampora

    Super Awesome!

  8. Rudra says:

    Very beautiful tip. Was aware of GoTo Sprecial but never knew that it can be used to solve this kind of problem. Thank you!!!!

  9. Raymond says:

    1 – Goto the most top blank cell in amount column (assume is column C)
    2 – type in formula =sumif(B:B,B11,C:C) (B11 is the most bottom cell of contain “Customer – 1″)
    3 – Copy the formula cell
    4 – Select column C blank cell by Control-G Goto Special
    5- Paste the formula

  10. Geof says:

    Here is my attempt in VBA .
    It seems to work. Comments welcome.

    Sub addTotalsToDataBlocks()
    ‘————————————————————
    ‘Adds a sum function to the base of the rightmost column
    ‘of sporadic blocks of data within a worksheet
    ‘————————————————————
    Dim oArea As Range
    Selection.SpecialCells(xlCellTypeConstants).Select
    For Each oArea In Selection.Areas
    oArea.Cells(oArea.Rows.Count, oArea.Columns.Count).Offset(1, 0).Formula = “=sum(” & “R[” & -oArea.Rows.Count & “]C:R[-1]C)”
    Next
    End Sub

  11. LeonK says:

    What a great tip – I’ve got a regular data checking procedure which I can use this (VBA Solution) on, saving me hours.

    The MrExcel link resulted in me buying Bob Ulmas’s book ‘Excel Outside the box’. It’s thanks to Chandoo, MrExcel and other MVPs like Bob Ulmas that I’m where I am today.

    • Geof says:

      Hi

      Try this line in place of the existing Selection.SpecialCells( .. )

      Selection.SpecialCells(xlCellTypeConstants, xlNumbers).Select

      This overcomes some problems I found where there were column and row headers. It now only selects numbers in each chunk.

      BTW it doesn’t like empty cells.
      Every day is a school day !!

      Cheers

  12. […] was sporadic totals week, and nobody told me! To see how to achieve this, you can watch videos by Chandoo, Mr. Excel and Kevin Lehrbass. Pick just one, or watch all three! There is a screen shot of […]

  13. Jorge Eduardo says:

    Hello ! I am Brazilian , and I’m sorry I do not speak English as I’m using an online translator .
    First wish a happy new year, or rather a happy every day . after all, time is something of the calendar.

    Only hope I can make myself understood , and the translator translated properly .
    is about conditional formatting formulas and macros .
    I know that would not be the appropriate place to ask the question but do not know where to ask .
    I already use conditional formatting with formulas , and I’m starting in vba in excel too, but not the point right .
    I even managed to create a formatting rule using macro . But …
    The manager conditional formatting rules seems not to exist for vba .

    I want to use the ability to adjust the formulas when changing the area of operation of the condition .
    the ability to be able to use vba identify colors , for searches with complex formulas in different areas of the worksheet.

    Does it have to do something?
    Anything will do , even if it is palliative.
    So long and thank you for taking your knowledge with others .

    Jorge Eduardo .

  14. Yatheesh says:

    The easiest way is to filter the data & apply subtotals, that should do the job quickly. Cant think of VBA’s in this situation :)

    • Jorge Eduardo says:

      Hello Yatheesh.
      Thanks for responding.
      But how do I do that? as I said, I’m new to excel, and also in vba. Despite the outlandish ideas. : P

      The idea is to use different formulas, to find items that are correlated by some factor. to make cross-checks.

      Happy New year

    • Jorge Eduardo says:

      Now I thought, was that reply for me?

    • Jorge Eduardo says:

      My idea can also be used to achieve subtotals.
      Using formulas to find and make formatting of the values ??that you want, use a macro to find the colors and pick values??, copy and paste these values ??in other place then make the calculations you want.
      or simply raved here.

  15. Hi Chandoo,

    Cool to share ideas even if accidentally! Once again, I really enjoyed your site in 2013 and I’m looking forward to your tips and humour in 2014.

    Cheers,
    Kevin Lehrbass

  16. Gijs says:

    Thanx for sharing this tip on using “F5 special”.
    I have a similar but different problem: a looong database where people marked modifications by changed formatting (eg red font or cell background).
    It would be nice if you could find abberant formatting with F5 special, but that doesn’t seem to be the case.
    Would anyone have an idea how to filter these, short of writing a VBA macro?
    Thanx, Gijs.

    • Jorge Eduardo says:

      I do not know if it works with F5 or is the case, You can leave a conditional formatting in place, she would act only if the value of a cell to be “x” or “y”.

      example:
      Select ($ b $ 12: $ b $ 120),
      going to create conditional formatting with formula put the formula = $ A $ 1 = “X”, create the formatting that you want.
      Now the B12 to B120 can put any formatting. but if you put “X” in A1 formatting will be the one you chose.

      Well I think you already know that. I’m the only beginner here. “But sometimes the ideas elude us, even the simplest”

    • Jorge Eduardo says:

      If it works, you can create a formula that format specific cells,

      Type: = AND (B $ 11 = $ B $ 1, $ A $ 1 = “x”) that will format the column below that has a value equal to ($ B $ 1) if the value of A1 is “X”. may be even more complex, for example seeking, higher value and lower than.

    • Jorge Eduardo says:

      I found my answer, the very answer I gave,

      something silly and easy to apply.

      no need to change the area of ??operation of the conditional form,

      just add a rule, and make it work like it’s INDEX and MATCH, and only do research in the area of ??the same name to one of the arguments, simple, fast and direct.

      • Gijs says:

        Jorge, I appreciate the input but none of what you say makes sense to me… The formatting is done already by others. I need to find the cells that have different formatting from all untouched ones. Regards, Gijs.

  17. Jorge Eduardo says:

    Sorry maybe could not understand, because of the online translation.

    if he’d like to post a sample spreadsheet, you would understand.

  18. S K srivastava says:

    While there could be many alternate methods of doing it but still the best method according to me is just use the subtotals tool. It works fine. I just tried it.

  19. Hi S K srivastava,

    Yes, Subtotals is a great feature and it’s often all that is required.

    However, the solution must fit the problem and sometimes the problem requires more steps. If for example the data is constantly changing or the data is in multiple columns then you would need something besides the subtotal function. This would either be VBA (Hui is a VBA expert here at Chandoo.org) or a series of formulas to automate the task.

    Cheers,
    Kevin

  20. Jorge Eduardo says:

    With my search for solution of my problem.
    I think I found the solution for you.
    The only thing I can do is formulas for conditional formatting. so I had to find solutions to various problems it. and unable to make self-sum with the values ??found by the formulas and has an easy to discover macro indendificar colors through conditional formatting.
    If you guys are interested talk to me if you accept excel “E”, “OU”(“AND”, “OR”) in formulas I send a sample spreadsheet, I sent another wrong, then blacked out.
    Can you make filtering even with> = <, well, all conditional formatting allows.

  21. Jorge Eduardo says:

    Create a macro that if you click a cell of “blue” color,
    she does sum or count the items that are in another color, “red”, for example.

    In B2 has (up), and C2 have (sum) sum the values ??of the top.
    In B2 has (up), and C2 have (count) count items from the top.
    In B2 has (low), and C2 have (sum) sum the items from below.
    In B2 has (low), and C2 have (cont) Count the items below.
    In B2 has (Lef), and C2 have (sum) sum the values ??of the left.
    In B2 has (Lef), and C2 have (cont) Count the items on the left.

    but only by color

    I will modify, and make a formula in conditional formatting,
    will be able to sum ??or count in any part of the spreadsheet with filters formulas conditional formatting, if you want to send a basic spreadsheet, I rely on it.
    I will add function, X> = <Y

    I hope you have been understandable.
    I am using inline translator.

  22. Jorge Eduardo says:

    Se tiver alguém que entenda português BR,
    eu acho mais fácil usar a formatação condicional do Excel para filtrar o que se quer,
    E pelas cores, fazer a operação de soma das coluna ou das linha.

    eu sei como identificar as cores, descobri a base de muitas noites de sono,
    Se fizerem uma macro para pegar pela cor eu modifico e ainda faço uma formatação condicional para filtrar , com índice e tudo, eu sou fraco com macros.
    eu realmente não preciso de parte de soma
    eu vou corrigir as formulas para funcionar com OR e AND

    escrevam uma macro,
    Que, se clicar em uma célula de cor “azul” ou outra,
    ela faça “soma ou contagem”, dos valores ou itens, que estão em uma outra cor, “vermelho”, por exemplo.
    E se:
    Em B2 tiver (up), e C2 têm (s), ela some os valores de cima.
    Em B2 tiver (up), e C2 têm (c), ela conte os valores de cima.
    Em B2 tiver (b ou d), e C2 têm (s), ela some os valores abaixo.
    Em B2 tiver (b ou d), e C2 tem (c), ela Conte os itens abaixo.
    Em B2 tiver (e ou L), e C2 têm (s), ela some os valores à esquerda.
    Em B2 tiver (e ou L), e C2 tem (c), ela conte os itens à esquerda.
    mas apenas pela cor,
    estou colocado assim para se adaptar a qualquer planilha, já que vou fazer para que trabalhe até a linha 6000, e coluna zz,
    Não vou colocar outros tipos de formulas nem outras macros, apenas modificar a macro para ela trabalhar com formatação condicional e fazer uma formula que trabalhe como índice vertical e horizontal.

    Se alguém se interessar. vou colocar colocar bastantes opções na formula para se testar,
    caso vc´s fiquem felizes e eu acredito nisso. eu queria pedi uma macro quase a mesma coisa, mas eu não preciso de soma nem nada disso.
    Jorge Eduardo

  23. Rajkumar S says:

    Hey Chandoo,

    You are doing great job by giving lots of sample templates. I have done some dashboards for my company using ur templates and putting some of my ideas.
    May God bless you and your family.

    Thanks once again. I learnt a lot.

  24. Zuber says:

    Thanks this is nice approche.

    I was think to select blanks only and entering some if logic with countifs formula

    -Zuber

  25. Purna this is my version of the video in Portuguese:
    https://www.youtube.com/watch?v=XyNDQOdUx1A

    Best regards

Leave a Reply