Formula for date selection

[Guest]

To summarise my earlier post: I have a list of dates, each with a
corresponding value, and my aim is to add up the values that correspond to
each month. The dates are not necessarily in order.

I had a very helpful response last time which allowed me to add up all the
January values, all the February values etc, using the formula

=SUMPRODUCT((MONTH($A$1:$A$100)=1)*(B1:B100))

which checks cells A1:A100 and if the month is January (1) adds the values
in column B. Now this is great, but now I have the added problem of needing
to select a month from a particular year i.e. January 2007 and January 2008
need to be totalled seperately. I'm sure there must be a similar way to do
this but if not I'll have to resort to using the Julian date format and use
range tables, which I'd rather not have to do!

Thanks to anyone who is able to help!

 

[Guest]

=SUMPRODUCT((MONTH($A$1:$A$100)=1)*(Year($A$1:$A$100)=2007)*(B1:B100))

 

[Pete_UK]

Try this:

=SUMPRODUCT((MONTH($A$1:$A$100)=1)*(YEAR($A$1:$A$100)=2007)*(B1:B100))

or, rather than hardcode the search values within the formula, better
to put your month in one cell (X1) and the year in another (X2), like
so:

=SUMPRODUCT((MONTH($A$1:$A$100)=X1)*(YEAR($A$1:$A$100)=X2)*(B1:B100))

Hopr this helps.

Pete