Formula for projects completed by month

Im looking for a total # of projects that are completed by Month and year

Tags:

Best Answer

Answers

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    How do you have your sheet set up to track the data currently? Depending on how you have things, you might end up needing to have some helper columns to do what you want to do.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • TPALJA
    TPALJA ✭✭

    I have date columns as 5/31/23 columns as well as month/year columns 5 2023

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hey @TPALJA

    What about creating a Report? Then you can Filter by if the Projects say "Complete", then Group by the Year column, add a second-level Grouping for the Month column, and use Summarize to COUNT how many rows are in each Group.

    Here's a free webinar that goes through these functions: Redesigned Reports with Grouping and Summary Functions

    Cheers,

    Genevieve

  • TPALJA
    TPALJA ✭✭

    thanks for the tip on reporting. I cant seem to get it to appear correctly on the dashboard.

    I had created a formula for total # of jobs per month and a formula for those that are completed in separate reports. I was hoping to combined the two formulas, I tried to combine the two formulas but its not quite working for me.


    =COUNTIF({Lead Follow Up Range 2 Assigned to}, IFERROR(MONTH(@cell), 0) = 1)

    =COUNTIF({Job Status}, "Completed")

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TPALJA

    To combine two COUNTIF statements, you'll want to use COUNTIFS, plural.

    Try this:

    =COUNTIFS({Lead Follow Up Range 2 Assigned to}, IFERROR(MONTH(@cell), 0) = 1, {Job Status}, "Completed")

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!