SUMIF & MONTH() ?

[Guest]

Hi,

I have a spreadsheet that contains a list of dates in A column, text in B
column, and amounts in C column (simple bookkeeping).

On a different sheet in the workbook, I want to collect the totals by month
(for budgeting). How can I setup a formula that will SUMIF by MONTH ? I just
want it to scan the whole list and total the amounts for a given month but I
can't 'seem to figure it out.

Thank you,

D

 

[Guest]

Hey D -

You can use an 'array formula' for the calculation - here's a sample:

=SUM(IF(Sheet1!$A$4:$A$14=Sheet2!B3,Sheet1!$C$4:$C$14))

the trick to making this work is that after you compose the formula, you
MUST hold down your 'shift' and 'ctrl' key before pressing enter. Otherwise
you'll error out. Also, col. A of your source data must be Jan, Feb, Mar,
etc. and a similar table must be created on the sheet you want the data to
sum to. If you have dates like 01/5/05, or January 15, 2005 - make sure that
you had a col. adjacent where you can denote that data as just January, etc.

 

[Dave Peterson]

You may want to spend a little time learning about data|pivottables.

If you select your range (include one row of headers)
then data|pivottable
follow the wizard until you see a dialog with a Layout button on it.
click that button
drag the date "button" to the row field
drag the amount button to the data field
finish up that wizard.

Now you have individual dates in column A.
rightclick and group those dates by month and year.

If you can't group that data, then you probably have non-date stuff in your raw
data (blanks or text). Fix it and try again.

 

[Guest]

I had never seen the pivot table thing before ... I have been playing with
that. Very cool. Seems like a reasonable way to organize everything out of
one enormous list. Awesome. thank you!

 

[Dave Peterson]

Now you've done it....<vbg>:

Here are a few links:


Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx