Hi,

I'm working on a worksheet where column A contain all the dates of this
year (january 1 in A1, january 2 in A2, etc) an column B contains values
that I want to summate. However, I only want to summate the values in
column B from january 1 till today. The values in the other cells of
column B (corresponding to dates later in the year) should not be
summated (yet). So the summation should change automatically daily
(because the current date should now be included in the summation).
Does anybody know how I can achieve this?

Tnx,
John


--
jonnel
------------------------------------------------------------------------
jonnel's Profile: http://www.excelforum.com/member.php...o&userid=36946
View this thread: http://www.excelforum.com/showthread...hreadid=566570

Try:

=SUMPRODUCT(--(A1:A366<=Today()),--(B1:B366))

You cannot use "whole" columns with SUMPRODUCT i.e. A:A is not allowed.

or

=SUMIF(A:A,"<=" & TODAY(),B:B)

HTH

"jonnel" wrote:


Hi,

I'm working on a worksheet where column A contain all the dates of this
year (january 1 in A1, january 2 in A2, etc) an column B contains values
that I want to summate. However, I only want to summate the values in
column B from january 1 till today. The values in the other cells of
column B (corresponding to dates later in the year) should not be
summated (yet). So the summation should change automatically daily
(because the current date should now be included in the summation).
Does anybody know how I can achieve this?

Tnx,
John


--
jonnel
------------------------------------------------------------------------
jonnel's Profile: http://www.excelforum.com/member.php...o&userid=36946
View this thread: http://www.excelforum.com/showthread...hreadid=566570

Toppers Wrote:
Try:

=SUMPRODUCT(--(A1:A366<=Today()),--(B1:B366))

You cannot use "whole" columns with SUMPRODUCT i.e. A:A is not
allowed.

or

=SUMIF(A:A,"<=" & TODAY(),B:B)

HTH
[/color]

Hi,
The first formula doesn't work (I just copied and pasted it), but the
2nd one absolutely does! So thanks a lot! :)
John


--
jonnel
------------------------------------------------------------------------
jonnel's Profile: http://www.excelforum.com/member.php...o&userid=36946
View this thread: http://www.excelforum.com/showthread...hreadid=566570

.... SUMPRODUCT should work .. it does for me.

But you have a solution ...

"jonnel" wrote:


Toppers Wrote:
Try:

=SUMPRODUCT(--(A1:A366<=Today()),--(B1:B366))

You cannot use "whole" columns with SUMPRODUCT i.e. A:A is not
allowed.

or

=SUMIF(A:A,"<=" & TODAY(),B:B)

HTH


Hi,
The first formula doesn't work (I just copied and pasted it), but the
2nd one absolutely does! So thanks a lot! :)
John


--
jonnel
------------------------------------------------------------------------
jonnel's Profile: http://www.excelforum.com/member.php...o&userid=36946
View this thread: http://www.excelforum.com/showthread...hreadid=566570

[/color]