Count number of times "Yes" shows within a date range

Hello, I am trying to get a count of how many times Yes appears within a date range - i.e. July = 2 x Yes, August = 1 x Yes. I have been trying with the COUNTIF function but haven't worked it out yet. I am trying to return the value on a separate sheet to where the data is located. If anyone could assist that would be great!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Melanie Paff

    Did you try the COUNTIFS suggested above? Based on your set-up it looks like this should be the right formula for you!

    =COUNTIFS({Column with Yes}, "Yes", {Column with Dates}, IFERROR(MONTH(@cell), 0) = 7)

    Here's an article that goes through how to create the cross-sheet references, which is how you would select the columns {in these}.

    Cheers,

    Genevieve

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Melanie Paff 

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.


    Or if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.


    My Email for sharing : Bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Melanie Paff

    I agree that it would be useful to see your set-up! I ask because we would want to see how you're housing the date range. Is this in a Date type of column?

    If it is, then we could use the MONTH function to search for a specific month (ex. 1 = Jan). I would wrap an IFERROR around the MONTH in case it's reading any cells that don't have dates in them. We would do that like this:

    IFERROR(MONTH(@cell), 0)


    We would also use a COUNTIFS (plural) to search for your two criteria (Month and Yes).

    Try something like this for July:

    =COUNTIFS({Column with Yes}, "Yes", {Column with Dates}, IFERROR(MONTH(@cell), 0) = 7)


    Let me know if this makes sense or if you still need help!

    Cheers,

    Genevieve

  • Hello - thanks for your assistance. I have attached a download of the sheet. So I need to count how many times Yes (Is this a lost time incident?) appears from each month and reflect this on another sheet to then calculate in the LTIFR to display on dashboard.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Melanie Paff

    Did you try the COUNTIFS suggested above? Based on your set-up it looks like this should be the right formula for you!

    =COUNTIFS({Column with Yes}, "Yes", {Column with Dates}, IFERROR(MONTH(@cell), 0) = 7)

    Here's an article that goes through how to create the cross-sheet references, which is how you would select the columns {in these}.

    Cheers,

    Genevieve

  • Thank you everyone - this is really helpful!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!