SUM if date < 30 days away

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two columns, one with the amount due, and one with due dates. The due
dates are changing periodically, and I want my sum to only include amounts
that are due within 30 days from the current date.
 
Hi,

=sumproduct(--(a2:a1000<c2)*(b2:b1000))

assuming the column A has the dates, column B has the amounts and C2 has the
current date.

hth
regards from Bazil
Marcelo


"Meesheltx" escreveu:
 
I ginned up some dummy data and got the results you're looking for with
this formula:
=SUMPRODUCT($A$1:$A$6,--($B$1:$B$6<TODAY()+30))

Column A contains the figures, column B contains the dates- you'll need
to provide the appropriate ranges.
 
This is helpful. But I'm also wondering if there is a way to do this,
assuming that C2 does NOT have the current date. isn't there a way that you
can include in the formula something like:

=sumproduct(--(a2:a1000<(NOW+30)*(b2:b1000))

....I tried that, but I don't think I have the formula right for the "now+30"
part. How else would I do that?
 
=SUMPRODUCT(--(A2:A200>TODAY()),--(A2:A200<TODAY()+30),B2:B200)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
=sumproduct(--(a2:a1000>today()),--(A2:A1000<today()+30),B2:B1000)

hth
regards from Brazil
Marcelo


"Meesheltx" escreveu:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trailing 30 days sum calculation with excel 1
Calculating Days Late 1
Excel Excel due and overdue date colours 2
Calculating interest due on late loan payments 1
Countif 3
Excel How to create a Accounts Receivable to calculate days in which payments against invoice are received 0
Need help with access expression - HELP!! 0
Conditional Formatting 5

Back
Top