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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.9K Get Help
 378 Global Discussions
 208 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!