Help with SUMIFS

I am trying to collect the numerical value based on whether or not work was completed between two dates by a specific employee that was assigned to the work, using SUMIFS.

=SUMIFS({Numerical Size 1}, {Date Task 1 Complete}, >={Week 1 Start}, {Date Task 1 Complete}, <={Week 1 End}, {Assigned To}, Employee@row)

ALL results are coming up as "0", but I know for sure that there are values to calculate. Am I using this incorrectly?

Find more posts tagged with

Formulas

Accepted answers

jmyzk_cloudsmart_jp

Hi @Mariann Carmen

I modified the demo sheets to fit your setup.

a separate worksheet, Assigned To, Numerical Size 1 and Date Task 1 Complete are collected

https://app.smartsheet.com/b/publish?EQBCT=69d95b31a6774e40a464f2049a40dd78

I added helper columns like Week Start (Mondays) to help input the data.

SUMIFS Sheet

https://app.smartsheet.com/b/publish?EQBCT=ade9bbed50fc40b0ade5dbc4e82de835

The SUMIFS formula is as follows;

=IFERROR(SUMIFS({Employee Data : Numerical Size 1}, {Employee Data : Date Task 1 Complete}, WEEKNUMBER(@cell) = WEEKNUMBER([Week Start (Monday)]@row), {Employee Data : Assigned To}, [Assigned To]@row), "")

The IFERROR is for the top row where we do not have any data for dates and Assigned to.

The SUMIFS function sums the {Numerical Size 1}-range of the first sheet whose {Date Task 1 Complete}-range's week number is the same as this sheet's [Week Start (Monday)]'s week number and {Assigned to}-range is the same as the current row's.

"Smartsheet calculates week numbers based on Mondays," so this formula suits your needs.

https://help.smartsheet.com/function/weeknumber

Please note it is not recommended to try to get the Date Task 1 Completes as dates because there can be more than one date that meets the SUMIFS criteria. (You may be able to use the JOIN function as used in the first sheet's helper columns.)

All comments

Darren Mullen

@Mariann Carmen

Can you share the data (sheet) you are querying?

jmyzk_cloudsmart_jp

Hi @Mariann Carmen

I wish I could use the SUMIFS function that way!

Unfortunately, the SUMIFS function can not use a {range} in the criterion. As shown below, the criterion takes a " {range}, criterion" format, and the criterion must come from the sheet the SUMIFS function is placed on, not the sheet the range is cross-referencing. (The only exception is @cell, which allows you to reference each value within the range. But, in your case, this @cell does not help)

https://help.smartsheet.com/function/sumifs

SUMIFS(range, criterion_range1, criterion1, [criterion_range2, criterion2, ...])

As shown in the image, you use ranges in the criteria, which does not work as criteria and gives you zero.

=SUMIFS({Numerical Size 1}, {Date Task 1 Complete}, >={Week 1 Start}, {Date Task 1 Complete}, <={Week 1 End}, {Assigned To}, Employee@row)

So, the demo sheet below, which I fabricated based on your information, has a checkbox helper column, [This Week], to determine if the [Date Task 1 Complete] is completed this week.

[This Week] =AND([Week 1 Start]@row <= [Date Task 1 Complete]@row, [Date Task 1 Complete]@row <= [Week 1 End]@row)

https://app.smartsheet.com/b/publish?EQBCT=8205a2cb13d849b48784d513846840e9

I can use the helper column in the sheet with the SUMIFS function, as shown below.

[SUMIFS] =SUMIFS({Numerical Sise 1}, {Assigned To}, [Assigned To]@row, {This Week}, 1)
or
[SUMIFS]=SUMIFS({Numerical Sise 1}, {Assigned To}, [Assigned To]@row, {This Week}, true)

You can use 1 or true to check if a checkbox column is checked.

https://app.smartsheet.com/b/publish?EQBCT=39b3d59c65ae4814a555af83b755a2fb

Mariann Carmen

@jmyzk_cloudsmart_jp Thank you! I am taking a look at this now.

How do I get my Numerical Size 1 and Date Task 1 Complete into the correct columns automatically? I have this information in another worksheet, but don't know how to get them to auto populate. I feel like I'm missing a few steps.

Mariann Carmen

@jmyzk_cloudsmart_jp

To give you a little more context, this is the collection worksheet.

This is the worksheet where I tried to recreate your steps above.

And…I have a worksheet with Assigned To, Numerical Size 1 and Date Task 1 Complete are collected. How do I tie them all together?

Mariann Carmen

Hello @Paul Newcome!

Do you have any thoughts on this? I'm having a really hard time making the proposed solution work.

Mariann Carmen

I figured it out. Thank you @jmyzk_cloudsmart_jp

jmyzk_cloudsmart_jp

I'm glad that you figured it out!😁

Mariann Carmen

@jmyzk_cloudsmart_jp

I can recreate your solution (and it works the way you suggested it), but it doesn't quite fit what I have going on with my own worksheets, and I'm having a hard time making it work with my own. Is it possible to do it with the following two worksheets?

Week Start and Week End are known, along with Employee in this worksheet. There are 53 columns for Week 1 thru Week 53. For the example below, let's use Week 1.

In a separate worksheet, Assigned To, Numerical Size 1 and Date Task 1 Complete are collected along with a lot of other columns (non essential to this scenario, like Problem Statement and Success Criteria).

ALL of my date columns are Column Type Date (restricted to dates only), and I was trying to use your SUMIFS formula like this:

=SUMIFS({Numerical Size 1}, {Assigned To}, $Employee@row, {Date Task 1 Complete}, >=[Week Start (Monday)]$1, {Date Task 1 Complete}, <=[Week End (Sunday)]$1)

As you can see in the image above, I have a column for Active Week, but I wasn't using it because I will have many Date Task 1 Complete dates within a week for an Employee (for instance, I might have 12/30/24, 1/1/25 and 1/3/25 for a single week) and I need the sum of Numerical Size 1 for all dates to be added together if they fit the criteria.

Is this something that is achievable with what I have, and if so, can you help me further with the correct formula? I'm still getting a result of "0".

Mariann Carmen

@Genevieve P.

I saw that you answered a similar question in this post (https://community.smartsheet.com/discussion/70198/i-need-help-with-a-sumif-and-countif-formula-with-multiple-criteria-one-criteria-is-falling-between?), and I was wondering if you could see if there was something I'm doing wrong.

Any insights would be appreciated.

jmyzk_cloudsmart_jp

Hi @Mariann Carmen

I modified the demo sheets to fit your setup.

a separate worksheet, Assigned To, Numerical Size 1 and Date Task 1 Complete are collected

https://app.smartsheet.com/b/publish?EQBCT=69d95b31a6774e40a464f2049a40dd78

I added helper columns like Week Start (Mondays) to help input the data.

SUMIFS Sheet

https://app.smartsheet.com/b/publish?EQBCT=ade9bbed50fc40b0ade5dbc4e82de835

The SUMIFS formula is as follows;

=IFERROR(SUMIFS({Employee Data : Numerical Size 1}, {Employee Data : Date Task 1 Complete}, WEEKNUMBER(@cell) = WEEKNUMBER([Week Start (Monday)]@row), {Employee Data : Assigned To}, [Assigned To]@row), "")

The IFERROR is for the top row where we do not have any data for dates and Assigned to.

"Smartsheet calculates week numbers based on Mondays," so this formula suits your needs.

https://help.smartsheet.com/function/weeknumber

Mariann Carmen

Thank you @jmyzk_cloudsmart_jp! This did the trick, and I was able to make it work. ❤️

jmyzk_cloudsmart_jp

Happy to help!😁 @Mariann Carmen

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.