Excel - Need to sum totals by days - don't want to do it manually 2

[nate12345]

My spreadsheets look like this (the date is YYMMDD)

Col. A Col. B
040110 488.00
040110 343.33
040112 121.45
040112 0.00
040112 23.14
040113 938.87


I need to go in between each date and insert a row and then sum the totals for that day. This would only take a second if the amount of data was equivalent to the above, unfortunately it's hundreds of days and over a dozen files.

I am thinking of combining all the files and adding an identifier field so that later I can sort it out and move them back into their own separate file. Even doing this though will mean I'll have over 20,000 rows and still hundreds of days to break up and assign unique SUM totals to each one.

Is there some type of automated way to do this in excel??? A macro perhaps?

Nate

 

[AnotherHiggins]

Have a look at Subtotals. For the data in your example, first add labels in Row 1 (let's say "Date" in column A and "Data" in column B).

Highlight the entire data set, then go to Data > Subtotals.

After each change in Date, you want to use the function Sum and add subtotal to Data.

At the left side of the screen there is now a little 1, 2 and 3. Click on the two to collapse the subtotals, getting rid of the individual data. Clicking on one shrinks it down to just the grand total.

Hope that helps.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[SkipVought]

Nate,

Just to set things straight, you do NOT have dates. Rather, you have is digits that seem to represent dates, but cannot function at all as real dates. For insance, you could not find the difference between two of your "dates" simply, like
[tt]
041010
050606
[/tt]
with REAL DATES, its a simple subtraction.

Could could not use your "dates" sucessfully with many of Excel's reporting and data manipulation features like Pivot Tables, or Charts.

You "dates" cold be converted to REAL DATES using the following formula, assuming that your "date" were in A1...
[tt]
=DATE(IF(LEFT(A1,2)<"50","20","19")&LEFT(A1,2),MID(A1,3,2),Right(A1,2))
[/tt]
You will discover that REAL DATES are just NUMBERS, like today is 38327.


Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[macropod]

Hi Skip,

They could be real dates, formatted as YYMMDD (which is basically an ISO date format).

If so, a formula in Column C like:
=IF(COUNTIF(A$1:A1,A1)=COUNTIF(A:A,A1),SUMIF(A:A,A1,B:B),"")
would put the required daily total adjacent to the last row
on which the values for the date appear. Not quite the solution that was sought, but a possible alternative solution.

Cheers

 

[SkipVought]

ISO date format is virtually useless in Excel except for DISPLAY purposes!

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[AnotherHiggins]

I'm a big fan of using real dates, and I probably should have mentioned it in my first post. But the fact is that Subtotal will work whether they are real dates or not. And based on this:

I need to go in between each date and insert a row and then sum the totals for that day.

I'd say Subtotal is exactly what Nate is looking for.


Having said that, Nate, I would recommend that you convert column A to actual dates if they are not already - it will make things much easier in the long run.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[SkipVought]

John,

I should have added the caveat that your solution was right on. My comment was "BTW..."

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[AnotherHiggins]

No problem, skip. My last post was directed more towards macropod. That's a cool formula, BTW, but I say use Excel's built in features when available - especially if it produces the desired results.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[nate12345]

Thank you Skip & Jon, that was exactly what I needed to know!!!