Count dates and another column

Paul Molloy

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 ;)

Genevieve P.

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

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.

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

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