Getting an Unparseable error with SUMIFS formula

Tori Lapolla

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!

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,))

Tori Lapolla

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

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