formula to group dollar amounts by date

[ginaburg]

I have an excel spreadsheet that has a column with a date and one with a dollar amount.
I would like to put summaries by month at the bottom of the page.
I would like to list each month
January $$
February $$
March $$
etc...
If the date falls in January add the amount field to the January summary field, if the date falls in february add the amount field to the february summary field, etc.

I hope this makes sense.
Thanks
Gina

 

[gizmo1973]

You could try this.

Set the format of your date to custom (mmmm)or add another coulmn with this format
Then insert a subtotal.

Data, Sub total, (criteria) Change of each "Month", Use Function Sum and add Sub Total to "Amount"

This assumes your 2 columns are headed "Month" and "Amount"
If not adjust Subtotals to accomodate

Proabably a quicker way but this should work

Regards, Phil

"Then I’d say what’s the point of having rank if you can’t pull it?"

(DCI Jack Meadows)

 

[gizmo1973]

Forgot to mention

Sort the columns by Date first!

Regards, Phil

"Then I’d say what’s the point of having rank if you can’t pull it?"

(DCI Jack Meadows)

 

[RamziSaab]

use the sumif funcntion

(goto help and type in sumif)

 

[ginaburg]

That works great.

I can get it to work with
>=06/01/04 as the criteria but how do I put in the criteria as a date range like:
>=06/01/04 or <=06/30/04

 

[RamziSaab]

hey try something like this: not sure if there is an easier way

=SUMIF(A1:A6;">06/01/04";B1:B6)-SUMIF(A1:A6; "<06/30/04";B1:B6)

if u need more help ask and i will try to have a deeper look into it

 

[SkipVought]

Hi,

I might use the PivotTable Wizard, Drag Date into the ROW AREA and Amount into the DATA AREA using Sum Of Amount.

Then Right Click Date heading in the PivotTable and select Group and Show Detail/Group and select Year & Month for the grouping

VOLA!

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[RamziSaab]

or u can use sumproduct

=SUMPRODUCT((A1:A6>=A1)*((A1:A6<=A3))*B1:B6)

should do the trick quite well, its an array formula so u haev to press crtl-shift-enter