Sumif for values in a certain year

[taylo40]

Hello,
Would appreciate some help with the sumif function.

In column A, I have a list of months:-

Jan-07
Feb-07

etc

In column B, I have the expenditure associated with that month

What I would like to do is in another cell enter a year "2007" and the formula would then add up all of the relevant months

Regards,

Jamie

 

[AnotherHiggins]

First, make sure that the dates are actually stored as dates.

If you click on one of those cells, do you see a full date - something like 1/1/2007 - in the formula bar? If not, you'll need to change that. See faq68-5827 for more info on how Excel deals with dates and times.

Assuming that column A does have actual dates, then you can use this:

[tab][COLOR=blue white]=SUMPRODUCT((YEAR(A1:A10) = 2007) * (B1:B10))[/color]


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[taylo40]

thanks John for the quick response

 

[taylo40]

Hello again,
One more question.

In the formula, the year "2007" is hard coded. What would the formula look like if I wanted to refer to a cell which would hold the year.

The cell could say "June 2007"

Regards

 

[GlennUK]

The cell could say "June 2007"

You'd use a function like this:

VALUE(RIGHT(TEXT(cell_ref,"yyyy"),4))

instead of the year.

So the final formula would look like:

=SUMPRODUCT((YEAR(A1:A10) = VALUE(RIGHT(TEXT(cell_ref,"yyyy"),4))) * (B1:B10))

Cheers, Glenn.

Beauty is in the eye of the beerholder.