Ask Your Question
0

Date difference

asked 2019-11-14 18:09:44 +0100

Heck gravatar image

updated 2019-11-15 01:39:30 +0100

erAck gravatar image

Hi I need to add he inputs from today and less than 30 days. the best formula I go too is

=SUMIF(A4:A11;">TODAY()-30";B4:B11)

 1 Oct 2019 2     10
10 Oct 2019 5
15 Oct 2019 6
01 Nov 2019 7
14 Nov 2019 3
15 Nov 2019 4
edit retag flag offensive close merge delete

Comments

Could you please format your input - or provide a sample file.

Opaque gravatar imageOpaque ( 2019-11-14 19:28:00 +0100 )edit

I just did that..

erAck gravatar imageerAck ( 2019-11-15 01:39:43 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2019-11-15 01:46:30 +0100

erAck gravatar image

First off, this

=SUMIF(A4:A11;">TODAY()-30";B4:B11)

won't compute, because ">TODAY()-30" is just a text string. This will do:

=SUMPRODUCT(A4:A11>(TODAY()-30);B4:B11)
edit flag offensive delete link more

Comments

It does not work. It is giving me 27 . The answer should be 20. ie 15 Oct - 15 Nov

Heck gravatar imageHeck ( 2019-11-15 07:14:52 +0100 )edit
1

It does not work

Please provide your data (a spreadsheet with the formula) that doesn't work for you. Your comment only means you did it wrong, or you have a different data layout.

And the answer should be 14, not 20: at 2019-11-15, 30 days ago was 2019-10-16.

FTR: here is working sample.

Mike Kaganski gravatar imageMike Kaganski ( 2019-11-15 07:19:10 +0100 )edit

Thanks. It's working

Heck gravatar imageHeck ( 2019-11-15 13:22:10 +0100 )edit

Mike's solution using

=SUMIF(A4:A11;">"&TODAY()-30;B4:B11)

is even better suited for large cell ranges because it avoids creating temporary internal arrays.

erAck gravatar imageerAck ( 2019-11-15 14:36:54 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-14 18:09:44 +0100

Seen: 39 times

Last updated: Nov 15