Sumif help!

[Rasec]

Interest Exp Interest Paid
Oct-11 3,155,272
Nov-11 3,114,455
Dec-11 3,114,455
Jan-12 2,960,442 -
Feb-12 2,960,442 -
Mar-12 2,960,442
Apr-12 3,047,221 -
May-12 3,047,221 -
Jun-12 3,047,221
Jul-12 3,097,646 -
Aug-12 3,097,646 -
Sep-12 3,097,646
Oct-12 3,079,469 -
Nov-12 3,079,469 -
Dec-12 3,079,469

-How can I tell excel to sum ONLY the interest expense for 2011. next to Dec-11? and the same thing for 2012?

PLEASE HELP! Thank you in advance!!!!

 

[Aladin Akyurek]

Interest Exp Interest Paid
Oct-11 3,155,272
Nov-11 3,114,455
Dec-11 3,114,455
Jan-12 2,960,442 -
Feb-12 2,960,442 -
Mar-12 2,960,442
Apr-12 3,047,221 -
May-12 3,047,221 -
Jun-12 3,047,221
Jul-12 3,097,646 -
Aug-12 3,097,646 -
Sep-12 3,097,646
Oct-12 3,079,469 -
Nov-12 3,079,469 -
Dec-12 3,079,469

-How can I tell excel to sum ONLY the interest expense for 2011. next to Dec-11? and the same thing for 2012?

PLEASE HELP! Thank you in advance!!!!

Try...

=SUMPRODUCT(--(YEAR($A$2:$A$16)=2011),$B$2:$B$16)

Repeat the formula set up for 2012. You might even consider having 2011 and 2012 in E2:E3 and replace 2011 with E2 in the above formula.

 

[Rasec]

YES SIR!!!! It works!!! but is there a way to drag the formula instead of copying/pasting/& changing the year?



Year Interest Exp
Oct-11 3,155,272
Nov-11 3,114,455
Dec-11 3,114,455 9,384,181
Jan-12 2,960,442
Feb-12 2,960,442
Mar-12 2,960,442
Apr-12 3,047,221
May-12 3,047,221
Jun-12 3,047,221
Jul-12 3,097,646
Aug-12 3,097,646
Sep-12 3,097,646
Oct-12 3,079,469
Nov-12 3,079,469
Dec-12 3,079,469 36,554,335
Jan-13 3,026,112
Feb-13 3,026,112
Mar-13 3,026,112
Apr-13 2,932,884
May-13 2,932,884
Jun-13 2,932,884
Jul-13 2,740,173
Aug-13 2,740,173
Sep-13 2,740,173
Oct-13 2,438,597
Nov-13 2,438,597
Dec-13 2,438,597 *I would like excel to know to sum that year here. Also i'd like to drag the formula*

Best! and THANK UUUUUUU!

 

[Aladin Akyurek]

YES SIR!!!! It works!!! but is there a way to drag the formula instead of copying/pasting/& changing the year?



Year Interest Exp
Oct-11 3,155,272
Nov-11 3,114,455
Dec-11 3,114,455 9,384,181
Jan-12 2,960,442
Feb-12 2,960,442
Mar-12 2,960,442
Apr-12 3,047,221
May-12 3,047,221
Jun-12 3,047,221
Jul-12 3,097,646
Aug-12 3,097,646
Sep-12 3,097,646
Oct-12 3,079,469
Nov-12 3,079,469
Dec-12 3,079,469 36,554,335
Jan-13 3,026,112
Feb-13 3,026,112
Mar-13 3,026,112
Apr-13 2,932,884
May-13 2,932,884
Jun-13 2,932,884
Jul-13 2,740,173
Aug-13 2,740,173
Sep-13 2,740,173
Oct-13 2,438,597
Nov-13 2,438,597
Dec-13 2,438,597 *I would like excel to know to sum that year here. Also i'd like to drag the formula*

Best! and THANK UUUUUUU!

E2: 2011
E3: 2012
E4: 2013

F2, just enter and copy down:

=SUMPRODUCT(--(YEAR($A$2:$A$16)=E2),$B$2:$B$16)

If you want to have the formula in column C...

C2, just enter and copy down:

=IF(YEAR(A2)<>YEAR(A3),SUMPRODUCT(--(YEAR($A$2:$A$16)=2011),$B$2:$B$16),"")

 

[Rasec]

THANK U SIR! would you be so kind to explain the logic?

=IF(YEAR(A2<>YEAR(A3),SUMPRODUCT((YEAR($A$2:$A$16)=2011),$B$2:$B$16,
)

 

[Rasec]

Is it possible to continue to use this formula:
=IF(YEAR(A2)<>YEAR(A3),SUMPRODUCT(--(YEAR($A$2:$A$16)=2011),$B$2:$B$16),"")

But have the =2011 change as the year changes? for this formula to work, i have to manually change the year.

THANKS a LOT!

 

[Aladin Akyurek]

Is it possible to continue to use this formula:
=IF(YEAR(A2)<>YEAR(A3),SUMPRODUCT(--(YEAR($A$2:$A$16)=2011),$B$2:$B$16),"")

But have the =2011 change as the year changes? for this formula to work, i have to manually change the year.

THANKS a LOT!

You are welcome.

It was the intent that 2011 would be replaced with a placeholder...

=IF(YEAR(A2)<>YEAR(A3),SUMPRODUCT(--(YEAR($A$2:$A$16)=A2),$B$2:$B$16),"")

 

[Biz]

How about?

=IF(YEAR(A2)<>YEAR(A3),SUMPRODUCT(--(YEAR($A$2:$A$16)=YEAR(A2)),$B$2:$B$16),"")

Biz

 

[Aladin Akyurek]

you are welcome.

It was the intent that 2011 would be replaced with a year expression as in the if-part...

=if(year(a2)<>year(a3),sumproduct(--(year($a$2:$a$16)=a2),$b$2:$b$16),"")

=if(year(a2)<>year(a3),sumproduct(--(year($a$2:$a$16)=year(a2)),$b$2:$b$16),"")