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!
Answers

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!

Oh, my apologies about the extra commas.... bad copy/paste job. Always happy to help!
Help Article Resources
Categories
Check out the Formula Handbook template!