Adding a 3rd criteria -- help with Syntax
Intent: Collect the amount of Non-Billed Payments for a specific timeframe if the payment type is 5.
*** Definitions: Separate Metrics sheet used to collect the following -- >
Sheet/Column {Non-Billed Payments Date Paid} is a date field in a referenced sheet
[Range Start] [Range End} are date fields for the first and last day of the billing period for the entire year in the sheet that contains the formula
Sheet/Column {Non-Billed Payment Amount} is the amount in the referenced sheet
Sheet/Column {Non-Billed Payment Type} contains the last criteria in the referenced sheet
*** This works:
=SUMIF({Non-Billed Payments Date Paid}, AND(@cell >= [Range Start]32, @cell <= [Range End]32), {Non-Billed Payment Amount})
*** This doesn't work (#UNPARSEABLE), some simple syntax error that I can't spot.
=SUMIF({Non-Billed Payments Date Paid}, AND(@cell >= [Range Start]32, @cell <= [Range End]32), {Non-Billed Payment Type} "5", {Non-Billed Payment Amount})
I also tried
=SUMIF@({Non-Billed Payments Date Paid}, AND(@cell >= [Range Start]32, @cell <= [Range End]32), {Non-Billed Payment Type} "5", {Non-Billed Payment Amount})
=SUMIF({Non-Billed Payments Date Paid}, AND(@cell >= [Range Start]32, @cell <= [Range End]32), AND{Non-Billed Payment Type} "5", {Non-Billed Payment Amount})
--Thanks in advance!
Best Answer
-
I think I understand what you are attempting to do, but please correct me if I am wrong.
Take a look at the difference between the SUMIF and SUMIFS functions.
In this case, you would want to use SUMIFS because you are comparing more than one condition.
=SUMIFS({Non-Billed Payment Amount}, {Non-Billed Payments Date Paid}, @cell >= [Range Start]32, {Non-Billed Payments Date Paid}, @cell <= [Range End]32, {Non-Billed Payment Type}, "5")
In face, your original formula, though it works with the AND function, would be better suited for SUMIFS as it is already comparing two conditions.... one for greater than and another for less than.
=SUMIFS({Non-Billed Payment Amount}, {Non-Billed Payments Date Paid}, @cell >= [Range Start]32, {Non-Billed Payments Date Paid}, @cell <= [Range End]32)
Answers
-
I think I understand what you are attempting to do, but please correct me if I am wrong.
Take a look at the difference between the SUMIF and SUMIFS functions.
In this case, you would want to use SUMIFS because you are comparing more than one condition.
=SUMIFS({Non-Billed Payment Amount}, {Non-Billed Payments Date Paid}, @cell >= [Range Start]32, {Non-Billed Payments Date Paid}, @cell <= [Range End]32, {Non-Billed Payment Type}, "5")
In face, your original formula, though it works with the AND function, would be better suited for SUMIFS as it is already comparing two conditions.... one for greater than and another for less than.
=SUMIFS({Non-Billed Payment Amount}, {Non-Billed Payments Date Paid}, @cell >= [Range Start]32, {Non-Billed Payments Date Paid}, @cell <= [Range End]32)
-
@Carson Penticuff Worked as expected. I also appreciate the link to SUMIF and SUMIFS and the improvement to my original formula.
-
Awesome @TamieC, I'm glad it's working for you. Regarding SUMIF/SUMIFS, I use SUMIFS exclusively. Even though it's purpose is for comparing more than one condition, it works for only one condition as well.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 349 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!