Help with Countifs and OR formula

Julie Hinton

Hello,

I need help! I have a sheet that shows multiple rows for each account with a status column and several product columns. On another sheet I want to count the number of rows containing the specific account ID where the status is "Active" and either the Product 1 or Product 2 cells are not blank. I have been successful counting the rows with just 1 product, but if I try to use the OR function to count if either of the cells is not blank I continue to get an error.

Here is the formula that was successful:

=COUNTIFS({Account Orders Range 3}, [Account ID]@row, {Account Orders Range 3}, "Active", {Account Orders Range 4}, @cell <> "")

I have tried using the OR function to add in the second Product cell to look for not blanks with this formula and have been unsuccessful:

=COUNTIFS({Account Orders Range 3}, [Account ID]@row, {Account Orders Range 3}, "Active", OR({Account Orders Range 4}, @cell <> "",{Account Orders Range 5}, @cell <> ""))

Do I just have a comma out of place, or is there an easier way to count the rows that contain:

1. A specific ID value
2. Have a specific "Status"
3. Also have text in column 1 or column 2

?

Julie Hinton

I was able to figure it out 😃

I found I was able to use OR with a range on another sheet, however I did not need that for this problem. I ended up using two COUNTIFS and summing the results.

The first COUNTIFS counts all of the rows where the cell in the Product 1 column is not blank. The second COUNTIFS counts all of the rows where the cell in Product 2 is not blank, but the cell in Product 1 is blank. The two countifs are added together. That solved it for me!

=COUNTIFS({Account Orders Range 3}, [Account ID]@row, {Account Orders Range 3}, "Active", {Account Orders Range 4}, @cell <> "") + =COUNTIFS({Account Orders Range 3}, [Account ID]@row, {Account Orders Range 3}, "Active", {Account Orders Range 5}, @cell <> "", {Account Orders Range 4}, @cell = "")

Mark Cronk

Hi @Julie Hinton ,

You can't use OR with a range and criteria. Try using a checkbox helper column [Order Check] with the formula: = IF(AND(ISBLANK([account order 1]@row), ISBLANK([account order 2]@row), 0,1). Change my column names for your actual names in order range 4 and 5. The result should be a check if either has data and no check if both are blank.

Then use this formula to count:

=COUNTIFS({Account Orders Range 3}, [Account ID]@row, {Account Orders Range 3}, "Active", {order check}, @cell=1). The order check external range will be your [order check] helper column.

Make sense? Work?

Mark

Julie Hinton

Hello @Mark Cronk

Unfortunately I cannot add a checkbox column to the source data sheet. Doing so will break several dependencies and another data source joined with this one. However; your suggestion did give me an idea that might work for another project I am working on. Thank you!

Is it possible to use the AND function with a range and criteria? I tried that and received #INVALID DATA TYPE

Are there any reference materials where I can see which functions can be used with a range and criteria and which cannot?

Mark Cronk

Good afternoon,

The Smartsheets formula list is: https://help.smartsheet.com/functions

I don't know of a way to do this without a helper column. Doesn't have to be a checkbox. However, there are plenty of very smart people in this Community. Let's see if someone else has a solution that can do an OR evaluation between ranges as part of a COUNTIFS formula.

Mark

Julie Hinton

I was able to figure it out 😃

I found I was able to use OR with a range on another sheet, however I did not need that for this problem. I ended up using two COUNTIFS and summing the results.

The first COUNTIFS counts all of the rows where the cell in the Product 1 column is not blank. The second COUNTIFS counts all of the rows where the cell in Product 2 is not blank, but the cell in Product 1 is blank. The two countifs are added together. That solved it for me!

=COUNTIFS({Account Orders Range 3}, [Account ID]@row, {Account Orders Range 3}, "Active", {Account Orders Range 4}, @cell <> "") + =COUNTIFS({Account Orders Range 3}, [Account ID]@row, {Account Orders Range 3}, "Active", {Account Orders Range 5}, @cell <> "", {Account Orders Range 4}, @cell = "")

Mark Cronk

Well done!

Mark

Amber Benge

@Julie Hinton I just used your solution to a similar issue. Worked well. Thank you!