SUMIFS Function Not Working

@khush_hello@khush_hello ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
05/13/19 Edited 12/09/19

Hi Community!

Please see below screenshots, 1st image has the SUMIFS formulae and columns used to create this, and 2nd image is the output result "TotalVacationhoursScheduled". The problem is this SUMIFS is not working as it is not adding up the number of hours from "VacationCalculatedHours" column back to "TotalVacationhoursScheduled". The criteria range is "EmployeeNameList" and criteria is a cell value from the "EmployeeNameList" . The problem remains all through that coloumn. Existing numbers that are currently displayed are hand coded ("+<numeric value> as shown below:

{=SUMIFS(VacationCalculatedHours:VacationCalculatedHours, EmployeeNameList:EmployeeNameList, <EmployeeNameList>) +<numeric value>}

 

Appreciate assistance.

Thanks!

Khush

 

SUMIFS Formulae.jpg

SUMIFS Result_.jpg

Tags:

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Khush,

    That's strange. It's working for me.

    I updated the formula with @row function to make it easier to reference the employee on the same row.

    =SUMIFS(VacationCalculatedHours:VacationCalculatedHours; EmployeeNameList:EmployeeNameList; Empl[email protected]) + 8

    The same version but with the below changes for your and others convenience.

    =SUMIFS(VacationCalculatedHours:VacationCalculatedHours, EmployeeNameList:EmployeeNameList, [email protected]) + 8

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Vacation Calculated Hours...

     

    What type of column is this and how are the numbers being populated?

    thinkspi.com

  • @khush_hello@khush_hello ✭✭✭✭✭

    Hi Andree,

    Thanks for your help. Unfortunately, the formulae is not working here.

    Further, I forgot to share, the "VacationCalculatedHours" column is a formulae based column & is Text/Number type. Is that the reason why it couldn't be working?

    Appreciate your response.

    Thanks!

    Khushboo

     

  • @khush_hello@khush_hello ✭✭✭✭✭

    Hi Paul,

    Appreciate your help. It is "Text/Number" column type and calculation/fomulae based column.

    Does this help?

    Thanks!

    Khush

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Happy to help!

    Can you paste the formula here?

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @khush_hello@khush_hello ✭✭✭✭✭

    Thanks Andree!

    So, basically the datasheet that has all these columns, have the first 12 rows, where I am trying to capture summary level data, where column "EmployeeNameList" representing an employee whose leave will be added starting from row#30 onwards; I have leave's entry for each employee(sample 3 employees only but in total 6 entries) starting from row#30 onwards, to summarize the overall hours on the first 12 rows column "TotalVacationhoursScheduled".

    Row#1:

    "EmployeeNameList", Formulae, {[email protected]} (Note: "EmployeeFullName" is a contact based column so to convert it to text I am using this method and storing datavalue to "EmployeeNameList"

    "VacationCalculatedHours", Formulae, {[email protected] + [email protected]}

    "TotalVacationhoursScheduled",

    Formaule,{=SUMIFS(VacationCalculatedHours:VacationCalculatedHours, EmployeeNameList:EmployeeNameList, $EmployeeNameList$1) + 0}

    Row#2, everthing remain same but just "$EmployeeNameList$2"

    Row#3,............................................"$EmployeeNameList$3"

    :

    :

    Row#12,........................................."$EmployeeNameList$12"

    _________________________End__________________________

    Hope this will provide detail behind the process.

    I look forward to your help!

    My only question is even if the associated column(s) that are being used in SUMIFS, are calculation based how does it affects the calcultaion?!

    Thanks!

    Khushboo

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    It's almost like the Vacation Calculated Hours are being treated as text. Try wrapping the formula in that column in a VALUE function.

     

    =VALUE([email protected] + [email protected])

    thinkspi.com

  • @khush_hello@khush_hello ✭✭✭✭✭
    edited 05/14/19

    Hi Paul

    THANKS MUCH:)!!!yes

    Such a simple point! I totally forgot about the data type while I was applying this SUMIFS calculation..Awesome.

    I learnt something interesting today!

    I wish Smartsheet could add another datatype altogether i.e. Text and Number as a seprate data type!

    Cheers!

    Khush

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    That would have been my next suggestion as well! wink

     

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

    thinkspi.com

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    yesyescool

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    coolcoolyes

    laugh

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.