Getting an Unparseable error with SUMIFS formula

HI

I am trying to calculate how much we have billed out, but not yet collected. We have 4 columns for each payment, up to 12 payments total. I want to add the Payment amounts together, only if Payment Due date is in the past and if Payment REC is blank. I've been playing around with the below formula, but can't seem to get it to work.

=SUMIFS([Payment 1 - AMOUNT]@row, [Payment 1 - DUE]@row, <TODAY, [Payment 1 - REC]@row, 0)+([Payment 2 - AMOUNT]@row, [Payment 2 - DUE]@row, <TODAY, [Payment 2 - REC]@row, 0)

Then this would continue until Payment 12. For the first row, I should get a sum of 23,273.78 as both payment 1 and 2 are past due



Any help would be appreciated!

Tags:

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Give this a try:

    =SUM(IF(AND([Payment 1 - DUE]@row < TODAY(), ISBLANK([Payment 1 - REC]@row)), [Payment 1 - AMOUNT]@row, 0,), IF(AND([Payment 2 - DUE]@row < TODAY(), ISBLANK([Payment 2 - REC]@row)), [Payment 2 - AMOUNT]@row, 0,), IF(AND([Payment 3 - DUE]@row < TODAY(), ISBLANK([Payment 3 - REC]@row)), [Payment 3 - AMOUNT]@row, 0,), IF(AND([Payment 4 - DUE]@row < TODAY(), ISBLANK([Payment 4 - REC]@row)), [Payment 4 - AMOUNT]@row, 0,), IF(AND([Payment 5 - DUE]@row < TODAY(), ISBLANK([Payment 5 - REC]@row)), [Payment 5 - AMOUNT]@row, 0,), IF(AND([Payment 6 - DUE]@row < TODAY(), ISBLANK([Payment 6 - REC]@row)), [Payment 6 - AMOUNT]@row, 0,), IF(AND([Payment 7 - DUE]@row < TODAY(), ISBLANK([Payment 7 - REC]@row)), [Payment 7 - AMOUNT]@row, 0,), IF(AND([Payment 8 - DUE]@row < TODAY(), ISBLANK([Payment 8 - REC]@row)), [Payment 8 - AMOUNT]@row, 0,), IF(AND([Payment 9 - DUE]@row < TODAY(), ISBLANK([Payment 9 - REC]@row)), [Payment 9 - AMOUNT]@row, 0,), IF(AND([Payment 10 - DUE]@row < TODAY(), ISBLANK([Payment 10 - REC]@row)), [Payment 10 - AMOUNT]@row, 0,), IF(AND([Payment 11 - DUE]@row < TODAY(), ISBLANK([Payment 11 - REC]@row)), [Payment 11 - AMOUNT]@row, 0,), IF(AND([Payment 12 - DUE]@row < TODAY(), ISBLANK([Payment 12 - REC]@row)), [Payment 12 - AMOUNT]@row, 0,))

  • Thank you very much Carson! It still came up as UNPARSABLE, but I just had to delete the extra comma after the 0 at the end of each phrase and it worked. I believe you also helped me with a formula a few months ago as well.


    Have a great day!

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Oh, my apologies about the extra commas.... bad copy/paste job. Always happy to help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!