Issues adding an IF to COUNTIF
Hi everyone,
I figured out how to use COUNTIF to count items within a column.
=COUNTIF({Task Tracking - Phase}, "Initiation") + COUNTIF({Task Tracking - Phase}, "Planning") + COUNTIF({Task Tracking - Phase}, "Execution") + COUNTIF({Task Tracking - Phase}, "Close Out")
I would like to create a formula that does this same thing but only count them if {Task Tracking - Business Unit} "Payments".
I tried this Formula
=IF({Project Tracking - Business Unit}, "Payments" COUNTIF({Task Tracking - Phase}, "Clout Out") + COUNTIF({Task Tracking - Phase}, "Execution"))
I get an "UNPARSEABLE error.
Any thoughts on is wrong with the syntax? Thanks in advance.
Best Answer
-
Try this:
=COUNTIFS({Task Tracking - Phase}, OR(@cell = "Initiation", @cell = "Planning", @cell = "Execution", @cell = "Close Out"), {Task Tracking - Business Unit}, "Payments"
Answers
-
So there is a comma missing in this I believe.
Without Screenshots my best guess is this:
{Project Tracking - Business Unit}: Assuming this is a Business Unit Column with Payments as an option in some cells.
You might want to try it again as this:
=IF({Project Tracking - Business Unit} = "Payments", COUNTIF({Task Tracking - Phase}, "Clout Out") + COUNTIF({Task Tracking - Phase}, "Execution"), "")This says if the business unit column has payments in it then add the counts of the phases with Clout Out to the Count of Phases with Execution. If the business unit column has anything else then return a blank.
I didn't test this on my end but give it a try!
-
Hi @Dakota Haeffner,
Thanks for the reply. I get a #INVALID OPERATION error when I try that. And to simplify things I trimmed down the formula to get it to a functional state before adding things to it.
=IF({Project Tracking - Business Unit} = "Payments", COUNTIF({Task Tracking - Phase}, "Execution"), "")
I still get the #INVALID OPERATION with this one. I added a screen shot to help clear up my ask.
Here is a little bit more info on what I am trying to do. The data in the image is used in a metric sheet that produces the graph in the pic. It's for all tasks sheet wide and works great. I am trying to do the same thing but wanting to produce a graph that returns these counts for just the "Payments" Business Unit. So far I am still having issues retuning a count for just the Payments business unit.
-
Try this:
=COUNTIFS({Task Tracking - Phase}, OR(@cell = "Initiation", @cell = "Planning", @cell = "Execution", @cell = "Close Out"), {Task Tracking - Business Unit}, "Payments"
-
@Nic Larsen , Thank you very much. This works perfectly. I am not sure how the COUNTIF and OR functions work together like that but they do. This was great. Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 435 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …