Sum log entries based on date

[eatmyjunk]

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

 

[Guest]

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.

 

[eatmyjunk]

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

 

[Guest]

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>>) -