Issue with Nested IF and COUNTIF Formula Not Working as Expected

Ask for Help
1

Hi everyone,

I’m struggling with a compliance audit formula that doesn’t seem to work as expected. The formula uses COUNTIF and nested IF statements to evaluate risks based on two criteria - Major and Critical fields. Here’s the revised formula:

IF(
COUNTIF(
{Major Field 1},
{Major Field 2},
{Major Field 3},
{Major Field 4},
{Major Field 5},
{Major Field 6},
{Major Field 7},
{Major Field 8},
{Major Field 9},
{Major Field 10},
{Major Field 11},
{Major Field 12},
{Major Field 13},
“MAJOR RISK IDENTIFIED”
) >= 3,
“AUDIT REFUSED”,
IF(
OR(
{Critical Field 1} = “CRITICAL RISK IDENTIFIED”,
{Critical Field 2} = “CRITICAL RISK IDENTIFIED”,
{Critical Field 3} = “CRITICAL RISK IDENTIFIED”,
{Critical Field 4} = “CRITICAL RISK IDENTIFIED”,
{Critical Field 5} = “CRITICAL RISK IDENTIFIED”,
{Critical Field 6} = “CRITICAL RISK IDENTIFIED”
),
“AUDIT REFUSED”,
“AUDIT VALIDATED”
)
)

Despite the conditions being met, the formula consistently returns “AUDIT VALIDATED”, and I can’t figure out why. Any insights or suggestions would be greatly appreciated!

Thanks in advance!

_ Just to clarify, the actual formula I am using is:
IF(
COUNTIF(
{Présence PGCS},
{Vérification périodique engin},
{État engin},
{Surveillant nacelle},
{Habilitation hauteur},
{Signalisation},
{Zone intervention condamnée},
{Véhicules équipés},
{EPI intervenants},
{Respect circulation},
{Présence extincteur},
{Présence trousse de secours},
{Propreté du chantier},
“NON-CONFORME”
) >= 3,
“AUDIT REFUSÉ”,
IF(
OR(
{Présence PP signé} = “NON-CONFORME”,
{Mesures connues et appliqués} = “NON-CONFORME”,
{Autorisation stationnement} = “NON-CONFORME”,
{Arrêté de circulation disponible sur site} = “NON-CONFORME”,
{Habilitation conducteur engin} = “NON-CONFORME”,
{Coupure émission onde} = “NON-CONFORME”
),
“AUDIT REFUSÉ”,
“AUDIT VALIDÉ”
)
)

This version uses “NON-CONFORME” as the condition to check against, unlike the English version provided earlier. Please consider this when offering suggestions or advice.

1 Like
2

Hey @Nicolas :wave: Great question.

Have you tried separating the formula into a few parts to verify it works as expected?
Such that the COUNTIF counts correctly and so on?
I’ve checked a similar formula on our end and it works okay

3

I have tried again, but it still doesn’t work. Can ‘Single Option Select’ fields impact the formula?

4

Actually yes! When you use a single-option field in a Noloco formula (for now - we plan to improve this) you must compare it to the “Internal” name, which is upper-snake case.

So if you have an option named Status it would be STATUS or if you had Not Complete it would be NOT_COMPLETE etc…

I can get you the exact values you will need if you can let me know which table / field it is

5

Now I understand. The collection I want to do the formula with is “AVSE”.

The fields I’m using in my formula are:
{Présence PGCS},
{Vérification périodique engin},
{État engin},
{Surveillant nacelle},
{Habilitation hauteur},
{Signalisation},
{Zone intervention condamnée},
{Véhicules équipés},
{EPI intervenants},
{Respect circulation},
{Présence extincteur},
{Présence trousse de secours},
{Propreté du chantier},
{Présence PP signé}
{Mesures connues et appliqués}
{Autorisation stationnement}
{Arrêté de circulation disponible sur site}
{Habilitation conducteur engin}
{Coupure émission onde}

Each field has three possible answers:
[CONFORME]
[NON-CONFORME]
[NON-APPLICABLE]

6

very well it works except for the COUNTIF function which doesn’t seem to count the number of times NON_CONFORME is present but only if it appears once. Despite the fact that I indicate >=3

7

I got round the problem with the following formula, even though it’s not very clean code :

IF((IF(

Présence PGCS

=“NON_CONFORME”,1,0)+IF(

Vérification périodique engin

=“NON_CONFORME”,1,0)+IF(

État engin

=“NON_CONFORME”,1,0)+IF(

Surveillant nacelle

=“NON_CONFORME”,1,0)+IF(

Habilitation hauteur

=“NON_CONFORME”,1,0)+IF(

Signalisation

=“NON_CONFORME”,1,0)+IF(

Zone intervention condamnée

=“NON_CONFORME”,1,0)+IF(

Véhicules équipés

=“NON_CONFORME”,1,0)+IF(

EPI intervenants

=“NON_CONFORME”,1,0)+IF(

Respect circulation

=“NON_CONFORME”,1,0)+IF(

Présence extincteur

=“NON_CONFORME”,1,0)+IF(

Présence trousse de secour

=“NON_CONFORME”,1,0)+IF(

Propret du chantier

=“NON_CONFORME”,1,0))>=3,“AUDIT REFUSÉ”,IF(OR(

Présence PP signé

=“NON_CONFORME”,

Mesures connues et appliqués

=“NON_CONFORME”,

Arrêté de circulation disponible sur site

=“NON_CONFORME”,

Autorisation stationnement

=“NON_CONFORME”,

Habilitation conducteur engin

=“NON_CONFORME”,

Coupure émission onde

=“NON_CONFORME”),“AUDIT REFUSÉ”,“AUDIT VALIDÉ”))

1 Like