sumif question

[dasadler]

I have range A1:A5 containing dates such as 11/13/14, 1/4/15, and so on. In B1:B5. I have numbers... 2, 4, 6, etc.

I would like a formula to sum the numbers in B1:B5 if the year in A1:A5 is 2015. All the sumif variations I have tried were unsuccessful. I am having difficulty with the date year.

I know this is beyond simple but even my google searches have not helped.

[Rudi]

You need a sumifs:
=SUMIFS(B1:B5,A1:A5,">=01/01/2015",A1:A5,"<01/01/2016")

Alternative:
=SUMIF(A1:A5,">=01/01/2015",B1:B5) which will sum all values with a date AFTER 2014...

[dasadler]

Super. Thank you. I was thinking something like =SUMIF(A1:A5,"=year(2015)",B1:B5) but, of course, that was not it.

[rory]

You can also use SUMPRODUCT:
=SUMPRODUCT(--(YEAR(A1:A5)=2015),B1:B5)

[dasadler]

Thanks... what does the '--' do in the formula?

[HansV]

(YEAR(A1:A5)=2015) returns a series of 5 TRUE/FALSE values - TRUE for each cell in A1:A5 with a date in 2015, and FALSE for the others.
Although Excel stores TRUE as 1 and FALSE as 0, it doesn't treat TRUE and FALSE as numbers.
The minus sign in -(YEAR(A1:A5)=2015) forces Excel to treat the values and numbers. TRUE becomes -1 and FALSE becomes 0.
Finally, the second - changes the -1 values to +1; the 0s remain unchanged.
These +1s and 0s are multiplied with the corresponding values in B1:B5, then added together by SUMPRODUCT.

[dasadler]

Okay, Thanks. My understanding of the sumproduct function was that the corresponding elements in the arrays were multiplied then summed. I did not realize you can put a true/false condition in there.

[rory]

Okay, Thanks. My understanding of the sumproduct function was that the corresponding elements in the arrays were multiplied then summed. I did not realize you can put a true/false condition in there.

They are - that's why you need the -- as Hans explained to change the TRUE/FALSE to 1 and 0 for the multiplication.