Formule

TVP ALAN

In sheet A I have 400 lines where people have to check a box when they have received a document

at the and i want to create a dashboard where we can see how many docs we rcvd back?

We have three type of docs

GDP

TAPA

FRIGO

So i will have 3 colomn

the result i want to see is in this case is:

we had total 11 GDP docs

but we only rcvd 4 docs back so is 36%

For Tapa we had 4 TAPA shipments but 0 docs back ( as nothing has been thikked)

Help meeeeee

Thanks

Philip Robbins

@TVP ALAN do you have a column that identifies which document(s) are required for each line? Do you always need all three or are they mutually exclusive?

TVP ALAN

yes i have a column where is mentioned which service we give

If in column service niveau is GDP mentioned the field doc 065 GDP will be yellow

If Tapa is mentioned the column will be red

this to make admin more visible which tikbox needs to be ticked when we get the doc back

Philip Robbins

To do this in the same sheet you could add a summary row at the top and indent all the entries beneath it, then apply the following formula:

=COUNTIF(CHILDREN(), 1) / COUNTIF(CHILDREN([Service Niveau]@row), CONTAINS("GDP", @cell)) + ""

The only downside is that if you have the formula in a checklist column you cannot display the value as a percentage. An alternative would be to have a separate metric sheet and perform a similar calculation in a column that is of the text/number format.

TVP ALAN

how should the formula look like when i use a separate sheet

Philip Robbins

You'll need to know how to use cross-sheet references, but assuming you do, it'll be something like this:

=COUNTIF({Doc 65 GDP}, 1) / COUNTIF({Service Niveau}, CONTAINS("GDP", @cell)).

Note that the strings between the braces {} will be whatever you name the ranges in your current sheet when you reference them. I've just used your current column headers for clarity.

TVP ALAN

=COUNTIF({GDP Checklist Doc070 Check + KPI NIEUW Range 5}, 1) / COUNTIF({GDP Checklist Doc070 Check + KPI NIEUW Range 6}, CONTAINS("GDP", @cell)).

do something wrong as hey doesnt take

Philip Robbins

@TVP ALAN may I suggest you first read up on the use of cross-sheet references? Here would be a good place to start.

Curly braces denote external references in Smartsheet and while it is possible to perform mathematical functions on values and ranges from other sheets, it is not done by adjusting the reference names within the braces. If you want to check if more than one condition is valid, i.e. GDP Checklist Doc070 Check is checked and KPI NIEUW Range 5 is checked, you'll want to use a COUNTIFS function.

Beyond that, my earlier logic applies.