Excel filter: How do I add only data for a given month?
January 21, 2016 7:18 AM Subscribe
I have an Excel worksheet that logs hikes. The columns are date, location, miles, notes. The rows are hikes. I have a summary worksheet in the same workbook that I use to track progression. Things like cumulative miles for the year, average miles per month. I'd like to add month by month totals. I want to write a formula that will sum the distance only for a particular month (for example, January). I need a formula that will test if the hike occurred in that month, then add the miles to the sum. What would this formula look like?
On the summary worksheet, I have rows for each month (January, February, March, etc.). I don't mind hard coding the month into my formula for each row -- it's only 12. What I can't figure out is a) how to test the date in the log worksheet to see if it's in the month I want, and b) how to then add the miles for that date into an ongoing sum. I'd prefer not to hard code each hike -- I'd like this sum to update itself automatically.
On the summary worksheet, I have rows for each month (January, February, March, etc.). I don't mind hard coding the month into my formula for each row -- it's only 12. What I can't figure out is a) how to test the date in the log worksheet to see if it's in the month I want, and b) how to then add the miles for that date into an ongoing sum. I'd prefer not to hard code each hike -- I'd like this sum to update itself automatically.
You could also create a pivot table (go to the insert tab and choose pivot table), putting "months" in row labels and the miles under values.
posted by Cannon Fodder at 7:39 AM on January 21, 2016 [2 favorites]
posted by Cannon Fodder at 7:39 AM on January 21, 2016 [2 favorites]
The SUMIF formula is good for summing the values in a column based on the values in another column, like the mileage for all the hikes that happened on the first of the month. The SUMIFS formula is required if you have more than one criteria, in this case those that happened between two dates.
posted by soelo at 7:39 AM on January 21, 2016 [1 favorite]
posted by soelo at 7:39 AM on January 21, 2016 [1 favorite]
I would do this in two steps, for simplicity:
- Create a column called "Month" in your detail sheet, next to the "Date" column. Use the =MONTH(...) formula to populate it with the month number of the hike. Hide the column if you like.
- Use =SUMIF() on your summary sheet to add up the appropriate rows.
It's kind of hard to explain in detail - here's a sample Google sheet that shows the technique. The Google SUMIF and MONTH formulas are the same as Excel's.
posted by pocams at 8:14 AM on January 21, 2016 [1 favorite]
- Create a column called "Month" in your detail sheet, next to the "Date" column. Use the =MONTH(...) formula to populate it with the month number of the hike. Hide the column if you like.
- Use =SUMIF() on your summary sheet to add up the appropriate rows.
It's kind of hard to explain in detail - here's a sample Google sheet that shows the technique. The Google SUMIF and MONTH formulas are the same as Excel's.
posted by pocams at 8:14 AM on January 21, 2016 [1 favorite]
If you do a pivot table, you can group by month. I may misunderstand what you are trying to do, but if not, using a pivot table makes this sort of thing pretty easy.
posted by slipthought at 10:09 AM on January 21, 2016
posted by slipthought at 10:09 AM on January 21, 2016
« Older Recommendations for stories/podcasts to listen to | Using PayPal to send money to Ukraine Newer »
This thread is closed to new comments.
posted by soelo at 7:36 AM on January 21, 2016