Percentage of "Yes" Values in a Month
I have a sheet that includes a column (named "PTP") of Yes/No data.
I need a sheet summary formula that will calculate the percentage of "Yes" answers in that column, across rows that fall within a calendar month, per a date in a different column (named "Actual Start").
Said another way, I need this formula to search [Actual Start] for dates between the first and last days of a given month. I would then like it to tell me, within the identified range of rows, what percent of those [PTP] values = "Yes".
Can anyone help?
Best Answer
-
Try something like this...
=COUNTIFS(PTP:PTP, "Yes", [Actual Start]:[Actual Start], AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2021)) / COUNTIFS([Actual Start]:[Actual Start], AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2021))
Answers
-
Try something like this...
=COUNTIFS(PTP:PTP, "Yes", [Actual Start]:[Actual Start], AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2021)) / COUNTIFS([Actual Start]:[Actual Start], AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2021))
-
Thanks @Paul Newcome! That worked perfectly.
-
Happy to help. 👍️
-
Hi @Paul Newcome. This formula no longer works, for some reason... I get an #INVALID OPERATION error in my sheet summary field. Do you have any insight into why it's broken?
-
Is it that exact same formula, or has it been tweaked to fit new months?
-
@Paul Newcome I tweaked it for use with new months/years.
-
Are you able to copy/paste the new formula from the sheet?
-
Sure @Paul Newcome, but I get an #INVALID OPERATION error in my sheet summary field.
-
Right. I understand you are getting the error. But are you able to copy/paste the formula that is giving the error to here so I can see exactly what you have after your tweaks?
-
Apologies... I obviously misunderstood!
=COUNTIFS(PTP:PTP, "Yes", [Actual Start]:[Actual Start], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023)) / COUNTIFS([Actual Start]:[Actual Start], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))
-
I don't see any obvious issues, so lets just try adding an "@cell" reference to see if that helps.
=COUNTIFS(PTP:PTP, @cell = "Yes", [Actual Start]:[Actual Start], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023)) / COUNTIFS([Actual Start]:[Actual Start], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 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!