SUMIFS Function Not Working

@khush_hello

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

 

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; EmployeeNameList@row) + 8

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

=SUMIFS(VacationCalculatedHours:VacationCalculatedHours, EmployeeNameList:EmployeeNameList, EmployeeNameList@row) + 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

Paul Newcome

Vacation Calculated Hours...

 

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

@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

Hi Paul,

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

Does this help?

Thanks!

Khush

Andrée Starå

Happy to help!

Can you paste the formula here?

Best,

Andrée

@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, {=EmployeeFullName@row} (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, {=VacationFullHoursScheduled@row + VacationHoursScheduled@row}

"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 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(VacationFullHoursScheduled@row + VacationHoursScheduled@row)

@khush_hello

Hi Paul

THANKS MUCH:)!!!

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å

That would have been my next suggestion as well! 

 

Paul Newcome

Happy to help! 

Paul Newcome

Andrée Starå