• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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


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?

1 Solution
put in g3:

If you upload a dummy worksheet (without confidential data) I can test it out for you.
Jorge PaulinoIT Pro/DeveloperCommented:
Check if it's something like this
whoops - try this one:

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Mohammed RahmanCommented:
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.

@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
@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 :)

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.

Rory ArchibaldCommented:
You could also use a pivot table for a summary by day (or month, or year etc)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now