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.
sumif question
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
sumif question
Don
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: sumif question
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...
=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...
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: sumif question
Super. Thank you. I was thinking something like =SUMIF(A1:A5,"=year(2015)",B1:B5) but, of course, that was not it.
Don
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: sumif question
You can also use SUMPRODUCT:
=SUMPRODUCT(--(YEAR(A1:A5)=2015),B1:B5)
=SUMPRODUCT(--(YEAR(A1:A5)=2015),B1:B5)
Regards,
Rory
Rory
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: sumif question
(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.
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: sumif question
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.
Don
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: sumif question
They are - that's why you need the -- as Hans explained to change the TRUE/FALSE to 1 and 0 for the multiplication.dasadler wrote: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.
Regards,
Rory
Rory