SUMIFS and Weekday to pull totals for report weekly

Megan TW

Hi all,

I am trying to figure out a way to 1). sum data coming in between Monday and Saturday) in the sheet summary, and 2). use this to create a report with totals based on our workweek (Monday-Saturday).

I created a helper column to utilize the "WEEKDAY" function ("Date Lab Results RCVD Helper"). Getting this to sum the data seemed to work fine with the following:

=SUMIFS([Quantity of Units]:[Quantity of Units], [Date Lab Results RCVD Helper]:[Date Lab Results RCVD Helper], @cell <= "2"

Can I use the TODAY function in addition to the above SUMIF to make sure only this week is being pulled?

([Date Lab Results RCVD]:[Date Lab Results RCVD], @cell = TODAY()-7)

I feel like I'm probably over thinking it.

Paul Newcome

Assuming that "Date Lab Results RCVD" is the name of your date column...

=SUMIFS([Quantity of Units]:[Quantity of Units], [Date Lab Results RCVD]:[Date Lab Results RCVD], AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Megan TW

Hey Paul, thanks for your help. I'm missing something here as my value returned is 0. :(

Paul Newcome

Make sure that the [Quantity of Units] column contains actual numerical values and that the [Date Lab Results RCVD] column contains actual date values.

Also look at the data and make sure that there are actually rows that meet the date criteria in the source data.

Megan TW

Paul,

This is what I did to get it to work:

=SUMIFS([Quantity of Units]:[Quantity of Units], [Date Lab Results RCVD]:[Date Lab Results RCVD], AND((IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))))

For whatever reason when I added in the IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) I kept getting 0. :/

Paul Newcome

It depends on how exactly you added it. It looks like you used an extra set of parenthesis in your formula, so it could be that they were misplaced with the YEAR function built in.