How can I get the sum of one column meeting two conditions
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
-
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")
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!