Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need to filter by month
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 |
#2
|
|||
|
|||
Need to filter by month
Mike,
Have you tried the subtotal from the data menu. You can subtotal for each change in month and then type? Tony "mikeybmdb" wrote: 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 |
#3
|
|||
|
|||
Need to filter by month
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) "mikeybmdb" wrote in message ... 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 |
#4
|
|||
|
|||
Need to filter by month
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. mikeybmdb wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
formula: First and last day in month | Excel Worksheet Functions | |||
Month Year Date Format | Excel Worksheet Functions | |||
Filter by month | Excel Discussion (Misc queries) | |||
How can i filter dates based on day of month | Excel Worksheet Functions |