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!