COUNTIFS problem

Lllllloyd

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

Jeff Reisman

@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")))

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?

Lllllloyd

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

@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")))

Lllllloyd

thank you sir. This worked for me