Calculating sums when the number of values to be added is variable

Posted on 2011-10-11
Last Modified: 2012-06-21

In the following screenshot of an Excel (2010) spreadsheet, all but two columns (C & F) of a bank account register are shown.  The remaining columns are present but have been hidden. a
Because there are multiple entries for many of the days, a third column (G) is designated to display daily totals.

The daily totals in the screenshot spreadsheet were created manually using =SUM() but what formula in column G would generate those totals automatically.  The problematic issue is that the number of values for each day is not constant and therefore the formula needs to be able to locate the first entry of the day and then take the sum from that entry down to the last entry of the day.  Which Excel function will do that?

Question by:Steve_Brady
    LVL 10

    Expert Comment

    put in g3:

    If you upload a dummy worksheet (without confidential data) I can test it out for you.
    LVL 48

    Expert Comment

    Check if it's something like this
    LVL 10

    Expert Comment

    whoops - try this one:

    LVL 10

    Expert Comment

    by:Mohammed Rahman
    Try this:

    Keep the "Date" and "Daily Total" column side by side.
    Eg: date column "C" and Daily Total Column "D"

    Enter all the dates in column C and the resective amounts in column D.

    Select column C and D - click on Data and then click on Subtotal.

    The first dropdown should be the "date", second drop down should be "Sum" and place a check mark on "amount" as in the screen shot below.
     excel 1  excel 2
    This is how the result will look like.
     excel 3

    You can follow this link : (I was not able to understand the entire stuff on the link below). May be it will help you or help another EE get an idea to provide a solutuion that suits your needs.
    LVL 41

    Expert Comment

    @Steve - not sure where your data starts/ends based on your picture, but lets assume the data starts on row 2 and goes down to row 1000 (you can certainly change the ranges, as needed, then the formula in Column F would be:

    [F2] =IF(ROW()=MAX(ROW($C$2:$C$1000)*($C$2:$C$1000=C2)),SUMIF($C$2:$C$1000,C2,F$2:F$1000),"")  <- then hit CTRL-SHIFT-ENTER to confirm array function, and it will then look like:


    Of course, you can then copy that formula down...

    The first part of the IF statement checks to see if this is the LAST occurrance of the date in the list:

    ROW()=MAX(ROW($C$2:$C$1000)*($C$2:$C$1000=C2))  <- gets the max row where the date is equal to the date being examined, then compares to current row

    See attached demo worksheet I built to mimic what was in your picture.



    The second part is fairly straight forward, and you may recognize SUMIF - testing column C - dates, against the current date, then sum column F
    LVL 41

    Expert Comment

    @Steve - there were some other viable solutions, if your data will always be sorted, whereas my solution is unique & correct for that and if the data can come out of sequence.  

    I have to admit, I may have overkilled the formula, otherwise :)

    LVL 3

    Accepted Solution

    Maybe it could be as simple as this:

    cell G126: =if(C126<>C127,SUMIF(C:C,C126,F:F),"")

    Fill down this formula for the entire list.

    LVL 85

    Expert Comment

    by:Rory Archibald
    You could also use a pivot table for a summary by day (or month, or year etc)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now