Formula help

SharonR

Hello, 

I have been trying to work this one out.  Attached is a picture of the column that i wish to tally.  This column is a checkbox column and there are multiple parent lines (these are the colours lines) The top line is the grandparent line the white rows are the children.

 

So what you see is each children row has some boxes check or not depending on completion of task for that provider, each provider is categorised into sectors.  I have used the following formula to get a total for each parent line ="" + COUNTIF(CHILDREN(), 1).

 

I need a formula that will add/count the parent lines and hopefully have the cell read "Total signed agreements: ??".  I have a feeling it has to do with my previous formula is the reason for my problem.

It should be a simple "Total signed Agreement: " + SUM(PARENT)

 

Help

 

Andrée Starå

Hi Sharon,

The formula must be in a separate column for it to work. If it's important to see the text in the checkbox column you can do it with a helper column.

Try this.

Count (Helper Column - Text/Number)

=COUNTIF(CHILDREN(Signed@row); 1)  (Placed in the parent cell of each section in the count column)

Signed (Checkbox column)

="Total: " + COUNTIF(CHILDREN(); 1) (Placed in the parent cell of each section)

="Total signed agreements: " + SUM(CHILDREN(Count@row)) (Placed in the top cell or where you like, to show the total signed)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Would that work for you?

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting