Nested SUMIFS / OR / WEEKNUMBER functions

Hi, how can I combine these two functions? They are both working separately for me for the same range of data.

=SUMIFS({MGHours}, {MGNum}, OR(@cell = [Project Num 1]@row , @cell = [Project Num 2]@row , @cell = [Project Num 3]@row , @cell = [Project Num 4]@row ))

=SUMIFS({MGHours}, {MGDate}, WEEKNUMBER(@cell ) = WEEKNUMBER(TODAY()))

Basically, I want to add the MGHours if the MGNum matches 1 of 4 project numbers, and date is also this week. (I actually want to be able to specify week number in a cell or sheet summary, but this would work for now.)

Thanks!

Find more posts tagged with

Sheets

Formulas

Accepted answers

KPH

Hi @Gloria H.

You can add the range and criterion from one to the end of the other, after adding a comma, for example:

Add the range and criteria from formula 2 to formula 1

=SUMIFS({MGHours}, {MGNum}, OR(@cell = [Project Num 1]@row , @cell = [Project Num 2]@row , @cell = [Project Num 3]@row , @cell = [Project Num 4]@row ), {MGDate}, WEEKNUMBER(@cell ) = WEEKNUMBER(TODAY()))

or, add the range and criteria from formula 1 to the end of formula 2

=SUMIFS({MGHours}, {MGDate}, WEEKNUMBER(@cell ) = WEEKNUMBER(TODAY()), {MGNum}, OR(@cell = [Project Num 1]@row , @cell = [Project Num 2]@row , @cell = [Project Num 3]@row , @cell = [Project Num 4]@row ))

Both formula will sum MGHours if MGNum matches the criteria in the OR and MGDate matches the weeknumber criteria.

I suggest adding an IFERROR to the WEEKNUMBER range in case you have blank cells as these will cause the formula to return Invalid Data Type errors.

=SUMIFS({MGHours}, {MGNum}, OR(@cell = [Project Num 1]@row , @cell = [Project Num 2]@row ), {MGDate}, IFERROR(WEEKNUMBER(@cell ), 0) = WEEKNUMBER(TODAY()))

And if you want to use a date other than today, you just change the TODAY() at the end to refer to the cell that has the date in.

Hope this helps.

KPH

Is Column6 a date format column with a date in row 2? The formula is expecting a date.

If the cell is just a week number, like 34, then instead of

= WEEKNUMBER([Column6]2)
you just need

= [Column6]2

All comments

KPH

Hi @Gloria H.

You can add the range and criterion from one to the end of the other, after adding a comma, for example:

Add the range and criteria from formula 2 to formula 1

or, add the range and criteria from formula 1 to the end of formula 2

Both formula will sum MGHours if MGNum matches the criteria in the OR and MGDate matches the weeknumber criteria.

I suggest adding an IFERROR to the WEEKNUMBER range in case you have blank cells as these will cause the formula to return Invalid Data Type errors.

=SUMIFS({MGHours}, {MGNum}, OR(@cell = [Project Num 1]@row , @cell = [Project Num 2]@row ), {MGDate}, IFERROR(WEEKNUMBER(@cell ), 0) = WEEKNUMBER(TODAY()))

And if you want to use a date other than today, you just change the TODAY() at the end to refer to the cell that has the date in.

Hope this helps.

Gloria H.

Thank you! That works great!

Would you be able to help me with the weeknumber? When replacing with just a single cell reference I am getting the invalid data type error. I tried with and without the IFERROR function.

=SUMIFS({MGHours}, {MGNum}, OR(@cell = [Project Num 1]@row , @cell = [Project Num 2]@row , @cell = [Project Num 3]@row , @cell = [Project Num 4]@row ), {MGDate}, WEEKNUMBER(@cell ) = WEEKNUMBER([Column6]2))

=SUMIFS({MGHours}, {MGNum}, OR(@cell = [Project Num 1]@row , @cell = [Project Num 2]@row ), {MGDate}, IFERROR(WEEKNUMBER(@cell ), 0) = WEEKNUMBER([Column6]2))

KPH

Is Column6 a date format column with a date in row 2? The formula is expecting a date.

If the cell is just a week number, like 34, then instead of

= WEEKNUMBER([Column6]2)
you just need

= [Column6]2

Gloria H.

That makes sense and solved it. Thanks very much for the help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.