Adding a 3rd criteria -- help with Syntax

TamieC

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!

Carson Penticuff

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.

https://help.smartsheet.com/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

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.

https://help.smartsheet.com/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)

TamieC

@Carson Penticuff Worked as expected. I also appreciate the link to SUMIF and SUMIFS and the improvement to my original formula.

Carson Penticuff

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.