COUNTIFS problem

I am trying to populate time est. cant seem to get the formula right. I am sure HAS got something to do with it.


Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Lllllloyd

    Ok, this looks like just a syntax issue, and if you want to consider more than one criteria, you need to use SUMIFS instead of SUMIF.

    Since one of your criteria columns is a multi-select, we'll need to add a HAS and an AND in there as well. HAS matches individual values from with a multi-select cell, AND tells the SUMIFS to consider rows where the workcenter column has both values. Using @cell in the HAS function tells the formula to use HAS on the cell values before it considers if the row matches the criteria.

    For column names that are more than one word or contain numbers or special characters, you need to wrap the column name in [square brackets].

    The logic - tell me if I'm wrong here:

    Add up the values in the [est, time(in hours)] column for rows where the [est, time(in hours)] value is greater than 1, and where the workcenter column contains both "New Tube Laser" and "Old Tube Laser". If so, try this:

    =SUMIFS([est, time(in hours)]:[est, time(in hours)], [est, time(in hours)]:[est, time(in hours)], >1, workcenter:workcenter, AND(HAS(@cell, "New Tube Laser"), HAS(@cell, "Old Tube Laser")))

    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 ✭✭✭✭✭✭

    @Lllllloyd

    What formulas have you tried that you can't seem to get right?

    Can you help us with at least a little information on how these two fields relate to each other?

    Any insight into the criteria?

    Why does "Old Tube Laser" and "New Tube Laser" make 32 on one line and 16 on the other?

    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!

  • thank you for the response Jeff. =SUMIFS(workcenter:workcenter,est,time(in hours):est, time(in hours),>1["new tube laser"]:[old tube laser"].I would much rather use Ruby sdk and API. I am new to the formula thing. I am trying to populate time to my dashboard automatically. simple sum this row if that row has this. Right now i am manually filtering the different workcenters and inputting the data. I need all of this to be automatic if at all possible.


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Lllllloyd

    Ok, this looks like just a syntax issue, and if you want to consider more than one criteria, you need to use SUMIFS instead of SUMIF.

    Since one of your criteria columns is a multi-select, we'll need to add a HAS and an AND in there as well. HAS matches individual values from with a multi-select cell, AND tells the SUMIFS to consider rows where the workcenter column has both values. Using @cell in the HAS function tells the formula to use HAS on the cell values before it considers if the row matches the criteria.

    For column names that are more than one word or contain numbers or special characters, you need to wrap the column name in [square brackets].

    The logic - tell me if I'm wrong here:

    Add up the values in the [est, time(in hours)] column for rows where the [est, time(in hours)] value is greater than 1, and where the workcenter column contains both "New Tube Laser" and "Old Tube Laser". If so, try this:

    =SUMIFS([est, time(in hours)]:[est, time(in hours)], [est, time(in hours)]:[est, time(in hours)], >1, workcenter:workcenter, AND(HAS(@cell, "New Tube Laser"), HAS(@cell, "Old Tube Laser")))

    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!

  • thank you sir. This worked for me

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!