Problems with SUMIF function

[Marcelo]

Hi, guys.

I'm having some problems with SUMIF function. Here goes the details:

I have a spreadsheet with dates in column B and values in column F. I'd like
to sum these values, but excluding future ones. So I thought about using the
following formula:

=SUMIF(B12:B60;"<=TODAY()";F12:F60)

It's not working as expected as it's returning 0 (zero). What's wrong in the
formula?

Thanks in advance!

Marcelo

 

[Dave Peterson]

Try:

=SUMIF(B12:B60;"<="&TODAY();F12:F60)

Inside the double quotes, excel is just seeing the text characters T-O-D-A-Y...

Same if you used a different cell:
=SUMIF(B12:B60;"<="&Z999;F12:F60)
or a date
=SUMIF(B12:B60;"<="&date(2009,3,31);F12:F60)

 

[Marcelo]

Thank you very much, Dave!

It worked!

My best regards,

Marcelo

 

[Shane Devenshire]

Hi,

Just to be cute - you can shorten the formula to

=SUMIF(B12:B60;"<="&NOW();F12:F60)

or

=SUMIF(B12:B60;"<="&H1;F12:F60)

And enter =NOW() in H1.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Thank you very much, Dave!

It worked!

My best regards,

Marcelo

 

[Marcelo]

Thanks, Shane!

Hi,

Just to be cute - you can shorten the formula to

=SUMIF(B12:B60;"<="&NOW();F12:F60)

or

=SUMIF(B12:B60;"<="&H1;F12:F60)

And enter =NOW() in H1.