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

edited 05/03/23

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])

• ✭✭✭✭✭✭

Is this what you need?

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

• ✭✭✭✭✭✭

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")

• ✭✭✭✭✭✭

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")

• ✭✭✭✭✭✭

Hi @AA_IT_2023,

This post may help you. One of the tips in it is that if any cell in your Invoice Date range is blank, the formula will break and you need to use an IfError statement.

IFERROR(MONTH(@cell), 0)=1

O

• This still returns Unparseable error

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

• ✭✭✭✭✭✭

Is this what you need?

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

• @ker9 Thank you that worked!

If i'm trying to get Multiple months or by quarter, how can I list it out? Would it be 1, 2 or 3 etc?

• ✭✭✭✭✭✭

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 Thank you! I appreciate the help on this.

• @ker9 - is there a way to exclude specific data from a column?

I want the sum of the billable total for the quarter, but exclude a specific project name from the sum. It would be under a different column (that would act as a filter).

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

• ✭✭✭✭✭✭

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")

• Thanks again for all the help.

• ✭✭✭✭✭✭

Your welcome! Best of luck with your project and hopefully you have a better understanding of the SumIfs function.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!