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

AA_IT_2023

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

Find more posts tagged with

Sheets

Sharing and Collaboration

Formulas

functionality

FAQ

Accepted answers

ker9

@AA_IT_2023

Is this what you need?

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

ker9

@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

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

All comments

ker9

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

https://community.smartsheet.com/discussion/66073/help-using-sumifs-with-month-today

AA_IT_2023

@ker9

This still returns Unparseable error

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

ker9

@AA_IT_2023

Is this what you need?

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

AA_IT_2023

@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?

ker9

@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:

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

AA_IT_2023

@ker9 Thank you! I appreciate the help on this.

AA_IT_2023

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

ker9

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

AA_IT_2023

Thanks again for all the help.

ker9

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.