Countifs/sumifs with a date range

RaffyM

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.

Jeff Reisman

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)

Jeff Reisman

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)

RaffyM

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.