Countifs projects starting or ending this week in sheet Summary formaulas

David Clunie

Hi all,

I need help on a formula to gather how many projects are due to start this week. I have this so far, but it returns 0, but one date range is within the parameters.

=COUNTIFS([Target Start Date]:[Target Start Date], WEEKNUMBER(TODAY()))

I also need to count on the [Target End date] on how many are due for completion this week.

All this has to be captured in sheet Summary formulas.

Kelly Moore

Hi @David Clunie

Try this

=COUNTIFS([Target Start Date]:[Target Start Date], WEEKNUMBER(TODAY()) = IFERROR(WEEKNUMBER(@cell), 0))

The piece missing from the formula above was the conversion of the [Target Start Date] into a WeekNumber so the formula could perform the comparison to WeekNumber (Today()). This is now accomplished using WEEKNUMBER(@cell).

I added the IFERROR to take care of any blanks or non-date entries in your date columns

cheers,

Kelly

Kelly Moore

Hi @David Clunie

Try this

=COUNTIFS([Target Start Date]:[Target Start Date], WEEKNUMBER(TODAY()) = IFERROR(WEEKNUMBER(@cell), 0))

The piece missing from the formula above was the conversion of the [Target Start Date] into a WeekNumber so the formula could perform the comparison to WeekNumber (Today()). This is now accomplished using WEEKNUMBER(@cell).

I added the IFERROR to take care of any blanks or non-date entries in your date columns

cheers,

Kelly