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

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

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