SUMIFS() FUNCTION with more than 5 CRITERION

Alponso Tolero

 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.

 

Thanks

 

Shaine Greenwood

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

Alponso Tolero

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

Alponso,

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

Craig