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

You could try the built in subtotals feature (Data | Subtotals) or a pivot
table (Data | PivotTable...). If you want to use SumIf:

=SumIf(<range of cells to test, Cell of the given year/month/etc, <range to
sum)

So:
A B C
1 2005 nov 100
2 2005 dec 120
3 2006 jan 250

=SumIf(A1:A3, A1, C1:C3) would give you 220 (total for 2005).

If you insert a column that combines the year and month, then you could
sumif to get just the 2005_Nov values, etc.

" 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

I looked back over my work, and I've got this SUMIF() construct
working already. What I am having trouble with is using the YEAR()
function in the 'cell of the given year/month/etc.' doesn't seem to
work. I have the entire year in a single cell, not split into multiple
cells. I'd also like to keep it that way. If you know how to do so,
I'm game to figure it out.

Cheers,
Martin

On Feb 26, 5:51 pm, BobT wrote:
You could try the built in subtotals feature (Data | Subtotals) or a pivot
table (Data | PivotTable...). If you want to use SumIf:

=SumIf(<range of cells to test, Cell of the given year/month/etc, <range to
sum)

So:
A B C
1 2005 nov 100
2 2005 dec 120
3 2006 jan 250

=SumIf(A1:A3, A1, C1:C3) would give you 220 (total for 2005).

If you insert a column that combines the year and month, then you could
sumif to get just the 2005_Nov values, etc.

" 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

Put in a couple cells the following dates (to calc for 2007):

2007/01/01
2007/12/31

Then use the following formula:

=SumIf (<<range, "<=" & <<Dec 31 cell, <<range to sum) -
Sumif(<<range, "<" & <<Jan 1 cell, <<range to sum)

" wrote:

I looked back over my work, and I've got this SUMIF() construct
working already. What I am having trouble with is using the YEAR()
function in the 'cell of the given year/month/etc.' doesn't seem to
work. I have the entire year in a single cell, not split into multiple
cells. I'd also like to keep it that way. If you know how to do so,
I'm game to figure it out.

Cheers,
Martin

On Feb 26, 5:51 pm, BobT wrote:
You could try the built in subtotals feature (Data | Subtotals) or a pivot
table (Data | PivotTable...). If you want to use SumIf:

=SumIf(<range of cells to test, Cell of the given year/month/etc, <range to
sum)

So:
A B C
1 2005 nov 100
2 2005 dec 120
3 2006 jan 250

=SumIf(A1:A3, A1, C1:C3) would give you 220 (total for 2005).

If you insert a column that combines the year and month, then you could
sumif to get just the 2005_Nov values, etc.

" 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