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.
Total count based on values from two columns
Hello,
I'd like to check the text in one column and a check mark in the second column and then get a total count based on that.
The first column is Scope which can be A or B. The second column is complete which is a checkbox that's either checked or unchecked. What I'd like the formula to do is check the Scope column. If it's A, then check if the the complete column is checked or unchecked. If it's both A and unchecked, those tasks need to be added so I know how many incomplete tasks there are.
Same for B. If the Scope is B and the complete column is unchecked, I'd like to add those tasks so I know how many incomplete tasks there are.
This is what I have so far:
=SUM(COUNTIF(Scope:Scope, “A”), IF(COUNTIF(Complete:Complete, 0)))
If a cell in the Scope column is A, check the status of the complete column and get the sum if it's unchecked. I just realized that this doesn't check the Scope column for B. There's no If else statement in Smartsheet; any ideas?
Thank you,
Reema
Comments
-
Reema,
Add New Column called "Incomplete" (type Checkbox)
Place this formulae in it
=IF(AND(OR(Scope# = "A",Scope# = "B"),Complete# = 0),1,0)
If using Hiearachy then use =Count(Children())
If not using Hierarchy use =Sum(Incomplete:Incomplete)
-
Hi James,
Where should I add =Sum(Incomplete:Incomplete)?
Thanks!
-
Reema,
Sorry use =Countif(Incomplete:incomple,1)
In any cell that will take the answer.
Not a checkbox as it can only accept 1,0 or text results.
Not a date field as it will convert the number to a date.
James
-
Hello Reema,
You can also take care of this with a COUNTIFS() formula, such as follows:
=COUNTIFS(Scope:Scope, “A”, Complete:Complete, 0)
This will count the number of times both conditions are met. You can copy this formula and replace the countif criteria (the "A" text and the number 0) as needed.
Note on the zero value in the formula: Smartsheet associates boolean values, such as checkbox cells, as numeric values of 0 if unchecked and 1 if checked. If you need to count checked, you'll want to use 1 instead of 0.
-
Hi Shaine,
I tried your solution but I got an #Unparseable error. I checked the names of the columns and the criteria and there aren't any spelling mistakes. What can I try?
Thank you.
-
Hello Reema,
Are you able to share your sheet with me at Support+Shaine@Smartsheet.com with Admin permissions?
Let me know the name of the column that your formula has been placed in, as well as the name of the sheet, and I'll be happy to take a look.
-
Hi Shaine,
I got it! It didn't work when I pasted your formula but it worked when I typed it in myself. Thank you for your help.
Reema
-
Yay! Glad it's working!
