[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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

Hello,

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?

Thanks
0
Steve_Brady
Asked:
Steve_Brady
1 Solution
 
etech0Commented:
put in g3:
=if(a3=a2,"",sumif(a:a,a2))

If you upload a dummy worksheet (without confidential data) I can test it out for you.
0
 
jpaulinoCommented:
Check if it's something like this
sumif.xlsx
0
 
etech0Commented:
whoops - try this one:

=if(a3=a2,"",sumif(a:a,a2,f:f))
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
OR

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.

http://www.computing.net/answers/office/adding-values-with-same-date/9631.html
0
 
dlmilleCommented:
@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:

{=IF(ROW()=MAX(ROW($C$2:$C$1000)*($C$2:$C$1000=C2)),SUMIF($C$2:$C$1000,C2,F$2:F$1000),"")}

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.

Enjoy!

Dave

The second part is fairly straight forward, and you may recognize SUMIF - testing column C - dates, against the current date, then sum column F
sumTotals-r1.xls
0
 
dlmilleCommented:
@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 :)

Dave
0
 
Davy2270Commented:
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.

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

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