COUNTIFS

Steve Stocks

Hi,

Not sure of the best method or formula, but I am trying to sum up (in column61) the total number of days worked in the last 2 weeks for everyone when the data looks like this:

I can do a countifs statement that returns the number of weekending dates that are within 2 weeks of the current rows weekending date, but not then be able to go on and add the number of days works on those rows.

=COUNTIFS([Weekending Date]:[Weekending Date], >([Weekending Date]@row - 14), [Weekending Date]:[Weekending Date], <([Weekending Date]@row + 1))

HELP!!!😁

Genevieve P.

Hi @Steve Stocks

Can I clarify, are you looking to SUM the data from the column [Week Total Decimal], but just for the two rows that are in the last two weeks relative to Today's date?

If so, we can use a SUMIF statement to find this data!

We will SUM the column [Week Total Decimal] if the date in the [Weekending Date] is within the last 14 days.

Try this:

=SUMIF([Weekending Date]:[Weekending Date], AND(@cell < TODAY(), @cell > TODAY(-14)), [Week Total Decimal]:[Week Total Decimal])

This looks for the date being less than TODAY but greater than 14 days ago.

Will this work for you? This would return only one number, so you wouldn't necessarily need an entire column for it. I would recommend using a Sheet Summary field, if you have access to this feature on your plan.

Cheers!

Genevieve

Genevieve P.

Hi @Steve Stocks

Can I clarify, are you looking to SUM the data from the column [Week Total Decimal], but just for the two rows that are in the last two weeks relative to Today's date?

If so, we can use a SUMIF statement to find this data!

We will SUM the column [Week Total Decimal] if the date in the [Weekending Date] is within the last 14 days.

Try this:

=SUMIF([Weekending Date]:[Weekending Date], AND(@cell < TODAY(), @cell > TODAY(-14)), [Week Total Decimal]:[Week Total Decimal])

This looks for the date being less than TODAY but greater than 14 days ago.

Will this work for you? This would return only one number, so you wouldn't necessarily need an entire column for it. I would recommend using a Sheet Summary field, if you have access to this feature on your plan.

Cheers!

Genevieve

Steve Stocks

Hi @Genevieve P,

Thanks for you response. Yes SUMIFS were the way forward in the end that I tried last night. Had to add a couple of columns to sum the weekly hours and number of days in that specific row. Then used

=SUMIFS([No Days Worked]:[No Days Worked], ([Weekending Date]:[Weekending Date]), >([Weekending Date]@row - 14), [Assigned To]:[Assigned To], [Assigned To]@row, ([Weekending Date]:[Weekending Date]), <([Weekending Date]@row + 1))

So this then looks at the total number of days a person has worked across all contracts (as there is multiple lines for same week if the person has worked on multiple contracts) in a two week window.

Thanks again for the response!

Steve

Genevieve P.

No problem! I'm glad that you were able to figure it out. 🙂