SUMIFS totals for week

ginamt3

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318862#Comment_318862

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:

https://help.smartsheet.com/articles/2476176-formula-error-messages

Jeff Reisman

@ginamt3

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. )

ginamt3

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318862#Comment_318862

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:

https://help.smartsheet.com/articles/2476176-formula-error-messages

ginamt3

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")