locked
Summing Excel columns within a time period RRS feed

  • Question

  • Hi,

    I have a spreadsheet which is a list of transactions; dates in column A, Amounts in column D.  I want a formula that gives me a total of everything within the last 30 days - a rolling 30-day total.  I wrote this but it isn't right and I can't figure out how to do it.  Any ideas please?

    =SUM(IF((TODAY()-A3:A1001)<=30,D3:D1001))

    Ta,

    Mike

    Sunday, October 4, 2020 5:12 PM

Answers

  • Thanks.  I eventually plumped for this:

    =SUMIFS(D:D,A:A,">="&TODAY()-30,A:A,"<="&TODAY())

    which will work no matter how long the column gets.

    • Marked as answer by MikeMay Monday, October 5, 2020 11:05 AM
    Monday, October 5, 2020 11:05 AM

All replies

  • It is an array formula, you should confirm it with Ctrl+Shift+Enter.

    But I think this ordinary formula is better:

    =SUMIFS(D3:D1001,A3:A1001,">="&TODAY()-30,A3:A1001,"<="&TODAY())


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Sunday, October 4, 2020 6:24 PM
  • Thanks.  I eventually plumped for this:

    =SUMIFS(D:D,A:A,">="&TODAY()-30,A:A,"<="&TODAY())

    which will work no matter how long the column gets.

    • Marked as answer by MikeMay Monday, October 5, 2020 11:05 AM
    Monday, October 5, 2020 11:05 AM