Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.


 Dear Everyone,


How to use sumifs() function with more than 5 criterion range.

It returns zero (0) even it is already satisfied with the data given for each column.


=SUMIFS($Qty:$Qty, $Road:$Road, [Look Roads]1, $[Cabinet No.]:$[Cabinet No.], [LkCabinet No.]1, $[H. H. No.]:$[H. H. No.], [LkH.H.No.]1, $[Line Type]:$[Line Type], LkTypes1, $WorkActivity:$WorkActivity, [Look Activity]1)


Please help.




sumifs problem.jpg

sumifs problem.jpg

sumifs problem.jpg


  • Hi Alphonso,

    You can add more than 5 criteria to a SUMIFS formula, and if all criteria are met, the appropriate column will be summed. When you use a cell reference as the criterion in a formula, it needs to match exactly to work. It's possible that one of the cells in your criterion doesn't exactly match its corresponding criterion range. This in turn causes the formula to think that nothing meets all of criteria and it returns a zero—since it won't sum when just one of the criteria is met.

    This could be something as simple as a formatting issue, or if you have a formula concatenating in one cell and the value manually typed in another, it may be off.

    You'll need to double-check all of your cell references to see which criteria doesn't quite match. You might troubleshoot this by temporarily breaking down your SUMIFS into individual SUM functions. You can add a single criterion to the SUM function and see if you get 24, then continue on until you find the criterion that returns 0.

    If you still run into issues after trying the above, reach out to our Support team: https://help.smartsheet.com/contact

  • Thanks for your reply.


    I figured out that my problem has nothing to do with the amount of criteria, it's the value itself that creates error.

    Like for example, if I tried to look for the value "004", it returns zero, but if I use the value "4", it returns one.



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭


    You can use text that looks like a number as a number using the VALUE() function.

    That is

    004 is text

    VALUE(004) is a number


This discussion has been closed.