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?
Best Answer
-
I modified the demo sheets to fit your setup.
a separate worksheet, Assigned To, Numerical Size 1 and Date Task 1 Complete
are collectedI added helper columns like Week Start (Mondays) to help input the data.
SUMIFS Sheet
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.
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.)
Answers
-
Can you share the data (sheet) you are querying?
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
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)
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)
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.
-
@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.
-
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?
-
Hello @Paul Newcome!
Do you have any thoughts on this? I'm having a really hard time making the proposed solution work.
-
I figured it out. Thank you @jmyzk_cloudsmart_jp
-
I'm glad that you figured it out!😁
-
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".
-
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.
-
I modified the demo sheets to fit your setup.
a separate worksheet, Assigned To, Numerical Size 1 and Date Task 1 Complete
are collectedI added helper columns like Week Start (Mondays) to help input the data.
SUMIFS Sheet
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.
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.)
-
Thank you @jmyzk_cloudsmart_jp! This did the trick, and I was able to make it work. ❤️
-
Happy to help!😁 @Mariann Carmen
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!