Xlookup that returns variable sum data

[David G.]

I am trying to create a formula that looks for todays date in a table and
returns the sum of daily entries for the month prior to the date. In the
sample below =vlookup(E1,A1:C2,2) instead of returning 6 value from row #2,
it will return 11, the sum of all values prior to the one assigned to the
date. Furthermore, can I set it to change the range tomorrow, adding yet
another cell to the sum for a return of 14?

1 2

A 11/20 5

B 11/21 6

C 11/22 3

D

E 11/20

Thanks

 

[Mike H]

Hi,

take your criteria out of the column then sum the entire column based on the
criteria

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

Sums all coulm B for a date in column A <= C1

Mike

 

[Eduardo]

Hi David,
I am confuse here, if todays date is 11/20 the result of you formula never
can be 11 since you are looking the sum of all the entries prior to that
date. let's say in
column A row 1 you have the Today date, then in column A row 4 you have
different dates all the way down and in column B you have the values.
Do this

In column B add a formula =A1-30. This will calculate todays day minus 30 days
Then in the cell you want to summarize the movement for the prior 30 days
enter

=SUMPRODUCT(--(A:A<A1),--(A:A>=B1),--B:B)

 

[David G.]

Mike,

It worked perfectly. How about if I need to add not the entire column, but
the one corresponding to today's date and the previous cell only? Can that
be done?

Thanks!