Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum log entries based on date
I (often) make lists that function as a log book (kilometers for car
travel, sales book, etc.), that I then need to summarise by date (year or month, etc.). I have been trying to figure out SUMIF(), and attempted the conditional sum wizard and playing around with YEAR() and MONTH(), but haven't had any luck. An example: Column A is the date, Column B is a dollar value. Below the list, I have 2 summary lines, one for 2005 (A10) and one for 2006 (A11). The formula should accomplish the following: If the date in Column A is in 2005 (hopefully using something like YEAR(A1:A5)=A10), then add, else ignore. I can get it working on a single entry, but can't seem to get a range to work. Here's what I have: =IF(YEAR(A1)=$A$10,SUM(B1),0) The problem is that it's line by line, and doesn't use a range. Not much of a summary! Cheers, Martin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum log entries based on date
Try this:
=SUMPRODUCT(--(YEAR(A1:A9)=2005),(B1:B9)) HTH, Elkar " wrote: I (often) make lists that function as a log book (kilometers for car travel, sales book, etc.), that I then need to summarise by date (year or month, etc.). I have been trying to figure out SUMIF(), and attempted the conditional sum wizard and playing around with YEAR() and MONTH(), but haven't had any luck. An example: Column A is the date, Column B is a dollar value. Below the list, I have 2 summary lines, one for 2005 (A10) and one for 2006 (A11). The formula should accomplish the following: If the date in Column A is in 2005 (hopefully using something like YEAR(A1:A5)=A10), then add, else ignore. I can get it working on a single entry, but can't seem to get a range to work. Here's what I have: =IF(YEAR(A1)=$A$10,SUM(B1),0) The problem is that it's line by line, and doesn't use a range. Not much of a summary! Cheers, Martin |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum log entries based on date
Works like a charm. Thanks!
But, I'm not sure I understand the two minus signs, or what they do. They do seem necessary, but not self explanatory. Cheers, Martin On Feb 26, 3:24 pm, Elkar wrote: Try this: =SUMPRODUCT(--(YEAR(A1:A9)=2005),(B1:B9)) HTH, Elkar " wrote: I (often) make lists that function as a log book (kilometers for car travel, sales book, etc.), that I then need to summarise by date (year or month, etc.). I have been trying to figure out SUMIF(), and attempted the conditional sum wizard and playing around with YEAR() and MONTH(), but haven't had any luck. An example: Column A is the date, Column B is a dollar value. Below the list, I have 2 summary lines, one for 2005 (A10) and one for 2006 (A11). The formula should accomplish the following: If the date in Column A is in 2005 (hopefully using something like YEAR(A1:A5)=A10), then add, else ignore. I can get it working on a single entry, but can't seem to get a range to work. Here's what I have: =IF(YEAR(A1)=$A$10,SUM(B1),0) The problem is that it's line by line, and doesn't use a range. Not much of a summary! Cheers, Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advanced filtering based on entries ABOVE those I wish to keep | Excel Discussion (Misc queries) | |||
Calculate a 30-day moving average based on the last x number of entries and date | Excel Worksheet Functions | |||
Counting unique entries based on given condition | Excel Discussion (Misc queries) | |||
Selecting rows based on cell entries | Excel Discussion (Misc queries) | |||
I Need to Count Number of Entries Based on Two Criteria | Excel Worksheet Functions |