Basic SUMIF problem

[guppyguy]

I have a table with column A being dates and column B being a dollar amount.
I want to only add up the rows for a user specified year.

SUMIF(YEAR(A:A),YEAR(TODAY()),B:B) doesn't work.

Been away from excel for too long-

Thanks!

Excel for Mac 2011

 

[T. Valko]

I have a table with column A being dates and column B being a dollar amount.
I want to only add up the rows for a user specified year.

SUMIF(YEAR(A:A),YEAR(TODAY()),B:B) doesn't work.

Been away from excel for too long-

Thanks!

Excel for Mac 2011

Try this...

=SUMPRODUCT(--(YEAR(A1:A100)=YEAR(NOW())),B1:B100)

I would avoid using entire columns as range references (unless you are using EVERY cell in the column).

 

[T. Valko]

Try this...

=SUMPRODUCT(--(YEAR(A1:A100)=YEAR(NOW())),B1:B100)

I would avoid using entire columns as range references (unless you are using EVERY cell in the column).

If you want the user to be able to enter a year then just refer to that cell:

D1 = user entered year number like 2010

=SUMPRODUCT(--(YEAR(A1:A100)=D1),B1:B100)

 

[guppyguy]

Thanks, it works.

What does the -- do? Haven't used that before.

 

[T. Valko]

Thanks, it works.

You're welcome!

What does the -- do? Haven't used that before.

See these:

http://xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html