Formula to tally dates within a certain date range and perform calculation

Hello,

I've been trying to write a formula to tally the number of cells with dates that are within the next 30 days and then take that number and multiply it by the amount in a column named "Installment Amt" and cannot get it to work using either of the following formulas:

=COUNTIFS({Pymt1 Date,Pymt2 Date,Pymt3 Date,Pymt4 Date,Pymt5 Date,Pymt6 Date,Pymt7 Date}, "<=" & TODAY() + 30) * [Installment Amt]

=SUMPRODUCT(({Pymt1 Date, Pymt2 Date, Pymt3 Date, Pymt4 Date, Pymt5 Date, Pymt6 Date, Pymt7 Date} <= TODAY() + 30) * [Installment Amt])

I'm getting an unparseable error for both. I've attached a screenshot if possible to take a look

{07FEC535-0779-4BC2-8E58-DF9768BA49C5}.png.jpg

Find more posts tagged with

Accepted answers

All comments

Amber Eakin

Hello @BB2791 - You want to check the dates in the columns "Pymt1 Date" to "Pymt7 Date" in a row to see if they're in the next 30 days; if so, you want to multiple that count by the installment amount for that row. Is that right? If so, give this a try:

=COUNTIF([Pymt1 Date]@row:[Pymt7 Date]@row, AND(@cell >= TODAY(), @cell <= TODAY(30))) * [Installment amt]@row

Did you need to do anything with the checkbox column? This formula will capture anything due in the next 30 days, even if it's already marked as paid (unless you have a different formula to change the payment date or something).

Please let me know if that helps!

BB2791

Hi Amber,

Thanks so much for getting back to me. The formula you provided works perfectly. I overlooked the need to discount payments in the next 30 days that have been paid. Is there a way to update the formula to account for that?

Also, in the time since I posted the original question the requirements for my sheet changed a bit in that there will now be multiple different installment amounts on the same row (referred to as Pymt1 Amt, Pymt2 Amt, etc). I will need to sum the different installment amounts that have the corresponding checkbox marked as paid (regardless of the date range). I will then need to take that sum and deduct it from the PO Amt value. This is way above my capacity with formulas so any help you can provide would be much appreciated!

BB2791

Forgot to attach a screenshot:

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.