COUNTIF FORMULA (3 Columns, 1 Column includes contact)
Hi All,
Need help with a formula!
I need a formula to summarize the following criteria. If Project Track = XX, and if Project Phase = XX and if Project Manager = X Person" what is the output?
My current formula, but it doesn't seem to be working. Is it due to the fact that the Project Manager filed is set to contact list?
=COUNTIFS([Project Track]:[Project Track], "T1-Concept", [Project Phase]:[Project Phase], "With Client", [Project Manager]:[Project Manager], "email address", [Project Track]:[Project Track], "T1-Concept", [Project Phase]:[Project Phase], "With Creative", [Project Manager]:[Project Manager], "email address")
Answers
-
I believe it is actually going to be an issue with the Project Phase portion. Based on your formula the Project Phase has to be both "With Client" and "With Creative" at the same time but only one of them which isn't possible. Are you trying to count if it is either one or the other? If so...
=COUNTIFS([Project Track]:[Project Track], "T1-Concept", [Project Phase]:[Project Phase], OR(@cell = "With Client", @cell = "With Creative"), [Project Manager]:[Project Manager], "email address")
-
Hey @Paul Newcome this formula would actually be for a sheet summary. Basically, the project track will always stay the same. But there are probably 15 or different project tracks. I need to be able to summarize those 15 which would be considered “active projects” within the drop down in project tracks there are 4 others options which are completed, cancelled thus I don’t need to count those. I just didn’t know what formula would be the best for my outcome!
-
I'm not sure I follow this part...
" within the drop down in project tracks there are 4 others options which are completed, cancelled thus I don’t need to count those"
Are you referring to the [Project Phase] column?
The above formula should work in a sheet summary field.
-
@Paul Newcome Sorry! Project Track column will always stay the same. But there are different variations within Project Phase. It can either be with client, with creative, with production, with PM, etc.
-
Ok. If you are trying to count all of them except for "Cancelled" and "Completed, then it would look like this...
=COUNTIFS([Project Track]:[Project Track], "T1-Concept", [Project Phase]:[Project Phase], OR(@cell <> "Cancelled", @cell <> "Completed"), [Project Manager]:[Project Manager], "email address")
If you have others, then you would include them in the OR statement using the @cell references.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!