What formula is best suitable for counting Yes and No and referencing column "Assigned To"
What formula is best suitable for counting Yes and No and referencing the column "Assigned To" name i.e Brandon = 6 "Yes" Cathy = 1 "Yes", Jimmy = 1 "No" and 6 "Yes"
Best Answer
-
There it is. Your second [%Complete] is missing the space in the column name.
Answers
-
What exactly is your end goal for these counts? If you are wanting to show them in a chart on a dashboard, you can use a row report and skip the formula entirely.
Otherwise one example of a formula would be a COUNTIFS.
=COUNTIFS(range 1, criteria 1, range 2, criteria 2)
-
Paul, not using in a chart or dashboard. Sole purpose is to count the # of Yes's for each PM to get a % of completion to tasks assigned as seen below - I got the Tasks Complete via a report, trying to simplify with the actual project worksheet as the report will not allow more than 1 column condition within filters.
-
I'm not sure I understand what you mean about the report filters, but the formula to use would be a COUNTIFS. Syntax for that is in my last comment.
-
Here is the COUNTIFS formula I am trying and it keeps coming back #UNPARSEABLE
=COUNTIFS([% Complete]:[%Complete], "Yes", {Assigned To}:{Assigned To}, HAS(@cell, "Jimmy Larimer"))
-
Is your formula on the same sheet as the source data or a different sheet?
Same sheet ranges:
[Column Name]:[Column Name]
Cross sheet ranges:
{Range Name}
-
Same sheet ranges:
[Column Name]:[Column Name]
=COUNTIFS([% Complete]:[%Complete], "Yes", [Assigned To]:[Assigned To], HAS(@cell, "Jimmy Larimer"))
-
Ok. And is the above not working?
-
it is not - it is coming back as #UNPARSEABLE
-
I don't see any immediate syntax issues. Are you able to provide a screenshot of the formula open in the sheet as if you are about to edit it?
-
Screenshot of formula -
Criteria using in the same sheet
Does it matter that % Complete column has a fx in it?
-
There it is. Your second [%Complete] is missing the space in the column name.
-
Paul thank you so much!
-
Happy to help. 👍️
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!