Calculating %Yes answers for each month on a spreadsheet (grid) with yearly data

Vinton Douglas
edited 06/20/22 in Formulas and Functions

I have a spreadsheet (grid) populated with "yes" and "no" based on the answers provided. This sheet has multiple data for the entire year i.e January to December. I need a formula to calculate the %yes for each month on the spreadsheet containing the yearly data.


Any help would be appreciated.

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would start by using a COUNTIFS to determine how many yes answers are in a single month and then divide that by a COUNTIFS that counts the total number of entries for the same month.

  • Jake Gustafson
    Jake Gustafson ✭✭✭✭✭✭

    Can you do a COUNTIFS formula to get your numerator (times 'Yes' was marked) and another COUNTIFS formula to get your denominator (times the field is not blank)?

    Something like this:

    =COUNTIFS(Month7:Month12, "January", [Status1]7:[Status1]12, "Yes") / COUNTIFS(Month7:Month12, "January", [Status1]7:[Status1]12, <>"")

    I'm not sure what your columns are and I wouldn't use static cell references, but you get the picture. The assumption is that you want to count the rows with Yes or No values only.

  • Thanks both. Works well.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!