How can I get the sum of one column meeting two conditions

AA_IT_2023 ✭✭
edited 05/03/23 in Formulas and Functions

Trying to get multiple conditions and sum up total billable.

Where Manager = John Smith in one column and Invoice Date = January in another column

Getting the #UNPARSEABLE error

=SUMIFS(AND(Manager:Manager, "John Smith", Invoice Date:Invoice Date, MONTH(@cell) = 1), [Billable Total]:[Billable Total])

Best Answers

  • ker9
    ker9 ✭✭✭✭✭✭
    edited 05/03/23 Answer ✓


    Is this what you need?

    =SUMIFS([Billable Total]:[Billable Total], Manager:Manager, "John Smith", [Invoice Date]:[Invoice Date], IFERROR(MONTH(@cell), 0) = 1)

  • ker9
    ker9 ✭✭✭✭✭✭
    edited 05/03/23 Answer ✓

    @AA_IT_2023 -

    If your quarters are based on a standard calendar year and you can use a helper column to determine quarter:

    Quarter including year:

    =IF(MONTH([Invoice Date]@row) > 9, "Q4-" + YEAR([Invoice Date]@row), IF(MONTH([Invoice Date]@row) > 6, "Q3-"+ YEAR([Invoice Date]@row), IF(MONTH([Invoice Date]@row) > 3, "Q2-"+ YEAR([Invoice Date]@row), "Q1-"+ YEAR([Invoice Date]@row))))

    Using the helper column containing the quarter (where QtrYr is the column name in my example)

    =SUMIFS([Billable Total]:[Billable Total], Manager:Manager, "John Smith", QtrYr:QtrYr, "Q1-2023")

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓

    Presuming your project column is named "Project" and the item you do not want is named "Not Me"

    =SUMIFS([Billable Total]:[Billable Total], Manager:Manager, "John Smith", QtrYr:QtrYr, "Q1-2023", Project:Project, <>"Not Me")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!