Countifs/sumifs with a date range

Options

Hello everyone. I'm fairly new into using the formulas and functions. Reading some concerns and responses, I able to write the formula correctly. However, is there a way to simply this so no need for me to manually change the date each week per month.

My target is:

Count/sum the number of rejected parts per assigned person on weekly basis. Here is my formula:

=COUNTIFS({Process Owner}, "Name", {Rejection Date}, AND(@cell >= DATE(2022, 1, 3), @cell <= DATE(2022, 1, 7)))

=SUMIFS({Total Cost}, {Rejection Date}, AND(@cell >= DATE(2022, 1, 3), @cell <= DATE(2022, 1, 7)), {Process Owner}, "Name")


Appreciate your inputs. Thank you in advance.

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    Use a helper column in your source sheet called "Rejection Week" to determine the week number of the Rejection Date for each row.

    =WEEKNUMBER([Rejection Date]@row)

    Then base your formulas off of comparing the rejection week number to the current week number.

    =COUNTIFS({Process Owner}, "Name", {Rejection Week}, WEEKNUMBER(TODAY()))

    =SUMIFS({Total Cost}, {Rejection Week}, WEEKNUMBER(TODAY()), {Process Owner}, "Name")

    If you're using this on kind of a metrics sheet for a list of employees, you can even modify it so that you don't have to put each employee's name into the formula. If you have a "Name" column listing your employees, you can reference that in the formula and put your COUNTIFS and SUMIFS on the same row with each employee name:

    =COUNTIFS({Process Owner}, Name@row, {Rejection Week}, WEEKNUMBER(TODAY()))

    =SUMIFS({Total Cost}, {Rejection Week}, WEEKNUMBER(TODAY()), {Process Owner}, Name@row)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    Use a helper column in your source sheet called "Rejection Week" to determine the week number of the Rejection Date for each row.

    =WEEKNUMBER([Rejection Date]@row)

    Then base your formulas off of comparing the rejection week number to the current week number.

    =COUNTIFS({Process Owner}, "Name", {Rejection Week}, WEEKNUMBER(TODAY()))

    =SUMIFS({Total Cost}, {Rejection Week}, WEEKNUMBER(TODAY()), {Process Owner}, "Name")

    If you're using this on kind of a metrics sheet for a list of employees, you can even modify it so that you don't have to put each employee's name into the formula. If you have a "Name" column listing your employees, you can reference that in the formula and put your COUNTIFS and SUMIFS on the same row with each employee name:

    =COUNTIFS({Process Owner}, Name@row, {Rejection Week}, WEEKNUMBER(TODAY()))

    =SUMIFS({Total Cost}, {Rejection Week}, WEEKNUMBER(TODAY()), {Process Owner}, Name@row)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • RaffyM
    RaffyM ✭✭✭✭✭
    Options

    Hi @Jeff Reisman, thank you for your answer. I have not use it yet but I assume it would work perfectly.

    I tweaked a lil bit my formula as I changed my target, counting the rejected parts in previous week and from day 1 to date. So far the new formula works.

    Previous week: =COUNTIFS({Process Owner}, "Name", {Rejection Date}, AND(@cell >= (TODAY() - 7), @cell <= (TODAY() - 3)))

    From Day 1 to Date: =COUNTIFS({Process Owner}, "Name", {Rejection Date}, AND(@cell >= DATE(2022, 1, 1), @cell <= TODAY()))

    I will try your suggestion later. Thanks again for the response.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!