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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    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

  • Jeff Reisman
    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. )

    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!

  • ginamt3
    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
    Jeff Reisman ✭✭✭✭✭✭
    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!

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!