Sumif that returns specific limited/rolling range
 

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, again on a
rolling basis? In other words, on the 21st it returns 11, on 22nd 9, on the
23rd 8 and so on. Can that be done?

Thanks.

"Mike H" wrote:

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

"David G." wrote:

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 11/23 5

E 11/20

Thanks

Sumif that returns specific limited/rolling range
 

Hi,

If there are no duplicate dates in the range try this with your date in C1

=SUMPRODUCT((A1:A13=C1)*(B1:B13)+(A1:A13=C1-1)*(B1:B13))

Mike

"David G." wrote:

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, again on a
rolling basis? In other words, on the 21st it returns 11, on 22nd 9, on the
23rd 8 and so on. Can that be done?

Thanks.

"Mike H" wrote:

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

"David G." wrote:

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 11/23 5

E 11/20

Thanks

Sumif that returns specific limited/rolling range
 

This will add the B value corresponding to the date entered in C1 plus the B
cell above
=INDEX(B:B,MATCH(C1,A:A))+INDEX(B:B,MATCH(C1,A:A)-1)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"David G." wrote in message
...
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, again on
a
rolling basis? In other words, on the 21st it returns 11, on 22nd 9, on
the
23rd 8 and so on. Can that be done?

Thanks.

"Mike H" wrote:

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

"David G." wrote:

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 11/23 5

E 11/20

Thanks

Sumif that returns specific limited/rolling range
 

=INDEX(B:B,MATCH(E1,A:A,0))+IF(MATCH(E1,A:A,0)1,I NDEX(B:B,MATCH(E1-1,A:A,0)),0)

--
__________________________________
HTH

Bob

"David G." wrote in message
...
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, again on
a
rolling basis? In other words, on the 21st it returns 11, on 22nd 9, on
the
23rd 8 and so on. Can that be done?

Thanks.

"Mike H" wrote:

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

"David G." wrote:

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 11/23 5

E 11/20

Thanks

Sumif that returns specific limited/rolling range
 

Well done Bob, I was sloppy in not thinking about the top value being in row
1!
best wishes
--
Bernard

"Bob Phillips" wrote in message
...
=INDEX(B:B,MATCH(E1,A:A,0))+IF(MATCH(E1,A:A,0)1,I NDEX(B:B,MATCH(E1-1,A:A,0)),0)

--
__________________________________
HTH

Bob

"David G." wrote in message
...
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, again
on a
rolling basis? In other words, on the 21st it returns 11, on 22nd 9, on
the
23rd 8 and so on. Can that be done?

Thanks.

"Mike H" wrote:

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

"David G." wrote:

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 11/23 5

E 11/20

Thanks