Need to filter by month

[mikeybmdb]

Hello,

I'm trying to analyze my monthly spending and type of spending. I have a
spreadsheet with a column for date, the amount spent and the type of
spending, but I'm unable to find a function which can filter each type of
spending by month. I've tried the sumif function, but this function does not
allow me to sum cells corresponding to other cells WITHIN a certain time
period. Instead it only allows me to sum cells corresponding to other cells
greater than, less than, or equal to a certain date. I've also tried the
conditional summer to build my own function, but I'm unable to make that work
either. I'm using Excel 2002. Please help.

Thanks,
-Mike

 

[Tony Gee]

Mike,

Have you tried the subtotal from the data menu. You can subtotal for each
change in month and then type?

Tony

 

[Bob Phillips]

Mike,

There are two simple choices that should work for you.

Use Pivot tables.

Use SUMPRODUCT. For instance

=SUMPRODUCT(--(MONTH(data_range)=month_num),--(type_range=type))

If you have data for multiple years, MONTH may not sufficient, as you might
get two different years, so you could then use

=SUMPRODUCT(--(TEXT(date_range,"yyyymmm")="2005Oct"),--(type_range="Rates"))

as an example.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

You could also insert a helper column and use this kind of formula:

=date(year(a2),month(a2),1)
And drag down.
This will be the first of each month. Format the column as "yyyy-mm" (or the
way you like).

Then sort by this column and apply data|subtotals.

By keeping the value in the cell a date, you'll be able to do any date
arithmetic later on. But by formatting it the way you want, it'll still look
pretty.