SUMIFS totals for week
I'm trying to get totals per week per engineer. I set up a table to get weekly amounts this formula is referencing another sheet. Job Value $, Engineer, Week. I'm not super proficient with formulas so any help would be appreciated
Here is the table to get the weekly totals for each engineer
Best Answer
-
The #INVALID DATA TYPE error indicates that you're trying to use a particular function on a data type it's not compatible with. For instance, trying to find the SUM of 2347 and the word "purple;" it's just not going to work.
Here you show the "Week" column you are referencing as having a value of "2/1/2022". Ordinarily, Smartsheet shows dates for us USA people as "02/01/22" with two-digit month, day, and year. That makes me suspect that it's either not a date-type column, or it is but the dates shown are actually just text values. The functions like WEEKNUMBER, DATEONLY, YEAR won't work against plain text values. So I would recommend checking that column and its data. It could be that however those dates are being entered or imported is setting them up as text instead of as true Smartsheet date values.
Here's a handy guide to Smartsheet formula errors:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
I'm trying to follow along what you're doing - it looks like your syntax is off in AND(IFERROR part. Let's see if we can fix this.
=SUMIFS({SHIPPED JOBS Range 1}, {MASTER DATABASE Range 1}, "Kyle Huskey", {MASTER DATABASE Range 2}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0), = 2022))
That looks better, you'll have to test and see.
The problem I saw was not closing out the formulas within your AND statement (not only does @cell need to be closed, but you have to close off the DATEONLY, the WEEKNUMBER, and after the ,0 you close out the first IFERROR. Same thing goes for the second IFERROR. )
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Jeff, thank you for your feedback.
I'm still getting Invalid data type.
I'm trying to add up the sum of the Job Value per week, per Engineer. I'm using the 3 columns below and referencing in the formula above.
Job Value. Engineer. Week
$10,000. Kyle Huskey. 2/1/2022
-
The #INVALID DATA TYPE error indicates that you're trying to use a particular function on a data type it's not compatible with. For instance, trying to find the SUM of 2347 and the word "purple;" it's just not going to work.
Here you show the "Week" column you are referencing as having a value of "2/1/2022". Ordinarily, Smartsheet shows dates for us USA people as "02/01/22" with two-digit month, day, and year. That makes me suspect that it's either not a date-type column, or it is but the dates shown are actually just text values. The functions like WEEKNUMBER, DATEONLY, YEAR won't work against plain text values. So I would recommend checking that column and its data. It could be that however those dates are being entered or imported is setting them up as text instead of as true Smartsheet date values.
Here's a handy guide to Smartsheet formula errors:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you. I got it going. Here it is for reference.
=SUMIFS({Master JOBV Range 1}, {MASTER SCHEDULE Range 1}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2021), {MASTER DATABASE Range 1}, "Kyle Huskey")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 361 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!