Count dates and another column

Hi,

I am trying to check a dated to see if its week 1, week 2 etc and then count the total from another column.

A formula that would look up column "date" to see if the date range is within project calendar week (1,2,3 etc) and then total the number of hours within that date range.

hope someone can help ;)

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Paul Molloy

    How are you identifying that "week number"? Is this something stored in your other sheet, or do you mean the week of the year? If you mean the week of the year, then we can use the WEEKNUMBER Function to search for a specific week's number and SUM together the values in the other column based on this number.

    For example, if you wanted to find the sum for this week (week 4), then you could do something like this:

    =SUMIF({Date Column}, WEEKNUMBER(@cell) = 4, {Column to Sum})

    or

    =SUMIF({Date Column}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), {Column to Sum})

    See: SUMIF Function and Formulas: Reference Data from Other Sheets

    Let me know if that makes sense!

    Cheers,

    Genevieve

  • Earl P
    Earl P ✭✭✭

    Hey Guys Im trying to get a count of 2 columns that have specific criteria. For example how many times in the month of march we had tasks which are training tasks.

    I created this formula but i'm only getting 0 as an output can anyone help?

    =COUNTIFS({Project Category}, "Course Enhancement", {Start Dates}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) - 2022))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Earl P

    At the end of your formula you have:

    IFERROR(YEAR(@cell), 0) - 2022))

    This is subtracting 2022 from your year, so if you have a date of April 2022, it's searching for April, 0000

    Try using an = sign instead:

    =COUNTIFS({Project Category}, "Course Enhancement", {Start Dates}, AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2022))


    Cheers,

    Genevieve

  • Earl P
    Earl P ✭✭✭

    Your amazing Thank you! Can't believe I Missed that!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!