Sumif that returns specific limited/rolling range

D

David G.

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.
 
M

Mike H

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
 
B

Bernard Liengme

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
 
B

Bob Phillips

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

Bernard Liengme

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