Percentage of "Yes" Values in a Month

Katherine Wilgruber

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?

Paul Newcome

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))

Paul Newcome

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))

Katherine Wilgruber

Thanks @Paul Newcome! That worked perfectly.

Paul Newcome

Happy to help. 👍️

Katherine Wilgruber

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?

Paul Newcome

Is it that exact same formula, or has it been tweaked to fit new months?

Katherine Wilgruber

@Paul Newcome I tweaked it for use with new months/years.

Paul Newcome

Are you able to copy/paste the new formula from the sheet?

Katherine Wilgruber

Sure @Paul Newcome, but I get an #INVALID OPERATION error in my sheet summary field.

Paul Newcome

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?

Katherine Wilgruber

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))

Paul Newcome

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))