# 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?

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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?

• edited 01/17/23

@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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!