Total count based on values from two columns

Reema Hussain

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

JamesR

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)

 

 

Reema Hussain

Hi James,

 

Where should I add =Sum(Incomplete:Incomplete)?

 

Thanks!

JamesR

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

Shaine Greenwood

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.

Reema Hussain

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.

Shaine Greenwood

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.

Reema Hussain

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

Shaine Greenwood

Yay! Glad it's working!