XL 2021 calculer nombre de cellules contant 1 dans colonnes non contigües

DUMART · 10 Février 2026

Bonjour, je cale sur une formule me permettant de calculer le nombre de cellules contant le chiffre 1 dans des cellules non contigües.

Ex ; col A B C D E F G H I J K L
********2 1 1 1 3 0 2 4 5 1 7 0

Le calcule consiste à vérifier dans les colonnes B D F H J L si la valeur est bien 1. Si oui déterminer le nombre de fois ou 1 apparait. Dans l'exemple le résultat est : 3. Cela sans tenir compte des cellules A C E G I K, car elles contiennent des 1 mais ne doivent être comptabilisées.

Si quelqu'un à la réponse, ce serait sympa.
Merci pour toutes vos propositions.

Saboh12617 · 10 Février 2026

Bonjour,
En stockant les positions de colonnes à garder dans une liste, on peut filtrer la plage puis faire l'équivalent d'un NB.SI avec SOMME :

Enrichi (BBcode):

=SOMME(1 * (FILTRE(A1:L1; {0\ 1\ 0\ 1\ 0\ 1\ 0\ 1\ 0\ 1\ 0\ 1}) = 1))

Ici j'ai indiqué dans {0\ 1\ 0\ 1\ 0\ 1\ 0\ 1\ 0\ 1\ 0\ 1} les colonnes à garder par un 1, et celles à skip par un 0.

Cousinhub · 10 Février 2026

Bonjour,
autre solution, compatible Excel2021 et +

Code:

=LET(a;A1:L1;NB(FILTRE(a;EST.PAIR(COLONNE(a))*a=1)))

Bonne apm

mapomme · 10 Février 2026

Bonjour ,
Ou plus classiquement :
=SOMMEPROD((B1:L999=1)*EST.PAIR(COLONNE(B1:L999)))

rem: 999 est un majorant du nombre de max de lignes susceptibles de contenir des données ( à adapter).

DUMART · 11 Février 2026

mapomme à dit:
Bonjour ,
Ou plus classiquement :
=SOMMEPROD((B1:L999=1)*EST.PAIR(COLONNE(B1:L999)))

rem: 999 est un majorant du nombre de max de lignes susceptibles de contenir des données ( à adapter).

DUMART · 11 Février 2026

Bonjour, mapomme.
J'ai utilisé ta formule cela fonctionne bien , sauf pour les cellules avec 0. Je pense que c'est dû au fait qu'il y a des cellules vides. Y aurait-il un moyen d'ignorer les cellules vides ?
merci pour ton aide déjà et si tu une solution ce serait top.
Michel

job75 · 11 Février 2026

Bonjour DUMART,

Si vous recherchez les valeurs 1 les cellules vides ou contenant 0 ne sont pas comptées.

Mais si vous recherchez les valeurs 0 les cellules vides sont comptées.

Pour ne pas les compter utilisez :

Code:

=SOMMEPROD((""&B1:L999="0")*EST.PAIR(COLONNE(B1:L999)))

ou bien :

Code:

=SOMMEPROD(EXACT(B1:L999;0)*EST.PAIR(COLONNE(B1:L999)))

A+

DUMART · 12 Février 2026

[td]

1

[/td][td]

1

[/td]

[td]

[/td]

[td]

2

[/td][td]

1

[/td][td]

[/td]
[td]

5

[/td][td]

0

[/td][td]

[/td]
[td]

6

[/td][td]

0

[/td][td]

[/td]
[td]

10

[/td][td]

3

[/td][td]

[/td]
[td]

12

[/td][td]

2

[/td][td]

[/td]
[td]

13

[/td][td]

3

[/td][td]

[/td]
[td]

18

[/td][td]

0

[/td][td]

[/td]
[td]

21

[/td][td]

2

[/td][td]

[/td]
[td]

24

[/td][td]

0

[/td]

[td]

[/td]

Voici une ligne de ce que j'ai. La recherche sur 0 même avec les nouvelles fonctions ne compte pas exactement.
Pour les 0 j'ai un résultat de 3 (au lieu de 4) et pour les 1 un résultat de 2(résultat exact), pour les 2 résultat de 1 (au lieu de 2), pour les résultat de 3 un résultat de 0 (au lieu de 2). J'ai bien entendu changer les valeurs 0, 1, 2, 3, à chaque formule.
J'ai utilisé : =SOMMEPROD((""&D101:AG101="0")*EST.PAIR(COLONNE(D101:AG101))) La colonne contenant le 1 en noir a gauche est D et la colonne vide à droite du 0 en rouge est AG. Ce qui donne le résultat ci dessous, sachant que le 3 correspond au 0, le 2 est le résultat pour 1, le 1 est le résultat pour le 2 et le 0 le résultat pour le 3. Merci de me dire si tu vois où est le problème.
Avec tous mes remerciements pour ton travail.
Michel

[td]

3

[/td]

[td]

2

[/td]

[td]

1

[/td]

[td]

0

[/td]

DUMART · 12 Février 2026

DUMART à dit:
[td]
1

[/td][td]
1

[/td]
[td]

[/td]
[td]
2

[/td][td]
1

[/td][td]

[/td]
[td]
5

[/td][td]
0

[/td][td]

[/td]
[td]
6

[/td][td]
0

[/td][td]

[/td]
[td]
10

[/td][td]
3

[/td][td]

[/td]
[td]
12

[/td][td]
2

[/td][td]

[/td]
[td]
13

[/td][td]
3

[/td][td]

[/td]
[td]
18

[/td][td]
0

[/td][td]

[/td]
[td]
21

[/td][td]
2

[/td][td]

[/td]
[td]
24

[/td][td]
0

[/td]
[td]

[/td]

Voici une ligne de ce que j'ai. La recherche sur 0 même avec les nouvelles fonctions ne compte pas exactement.
Pour les 0 j'ai un résultat de 3 (au lieu de 4) et pour les 1 un résultat de 2(résultat exact), pour les 2 résultat de 1 (au lieu de 2), pour les résultat de 3 un résultat de 0 (au lieu de 2). J'ai bien entendu changer les valeurs 0, 1, 2, 3, à chaque formule.
J'ai utilisé : =SOMMEPROD((""&D101:AG101="0")*EST.PAIR(COLONNE(D101:AG101))) La colonne contenant le 1 en noir a gauche est D et la colonne vide à droite du 0 en rouge est AG. Ce qui donne le résultat ci dessous, sachant que le 3 correspond au 0, le 2 est le résultat pour 1, le 1 est le résultat pour le 2 et le 0 le résultat pour le 3. Merci de me dire si tu vois où est le problème.
Avec tous mes remerciements pour ton travail.
Michel

[td]
3

[/td]
[td]
2

[/td]
[td]
1

[/td]
[td]
0

[/td]

Voici une copie d'cran car le message ci-avant semble avoir donné un résultat difficilement compréhensible.
En premier les chiffres en colonne de D à AG. Ensuite 3 2 1 0 est le résultat obtenu.

job75 · 12 Février 2026

Bonjour DUMART,

La formule utilise la fonction EST.PAIR donc seules les colonnes paires sont comptées.

Votre tableau commence en colonne D donc :

- le 1 en colonne E (5) n'est pas compté

- le 0 en colonne K (11) n'est pas compté

- le 3 en colonne Q (17) n'est pas compté

- le 3 en colonne W (23) n'est pas compté

- le 2 en colonne AC (29) n'est pas compté.

A+

laurent950 · 13 Février 2026

Bonsoir @job75 , @DUMART , le forum

Somme prod colonne non contigüe.
La Formule : pour la ligne 3 de la [Colonne de A à AC]
=SOMMEPROD((A3:AC3=0)*(MOD(COLONNE(A3:AC3)-COLONNE(A3);3)=1)) '........... / Résultat = 4 pour les 0
=SOMMEPROD((A3:AC3=1)*(MOD(COLONNE(A3:AC3)-COLONNE(A3);3)=1)) '........... / Résultat = 2 pour les 1
=SOMMEPROD((A3:AC3=2)*(MOD(COLONNE(A3:AC3)-COLONNE(A3);3)=1)) '........... / Résultat = 2 pour les 2
=SOMMEPROD((A3:AC3=3)*(MOD(COLONNE(A3:AC3)-COLONNE(A3);3)=1)) '........... / Résultat = 2 pour les 3

DUMART à dit:
Pour les 0 j'ai un résultat de 3 (au lieu de 4) et pour les 1 un résultat de 2(résultat exact), pour les 2 résultat de 1 (au lieu de 2), pour les résultat de 3 un résultat de 0 (au lieu de 2)

job75 · 13 Février 2026

Bonsoir laurent950,

Comme cela a été dit plus haut A3:AC3=0 comptera aussi les cellules vides, il faut ""&A3:AC3="0"

A+

Cousinhub · 13 Février 2026

Bonsoir,
Quitte à avoir 2021, autant en utiliser les avantages, non?
Cette formule devrait convenir, pour les valeurs 0 (à recopier vers la droite, en modifiant la valeur à dénombrer pour 1, 2, 3)

Code:

=LET(a;$D1:$AG1;NB(FILTRE(a;EST.IMPAIR(COLONNE(a))*(a=0))))

Bonne soirée

laurent950 · 13 Février 2026

Bonsoir @job75
Formule :
=SOMMEPROD((A3:AC3=0)*(MOD(COLONNE(A3:AC3)-COLONNE(A3);3)=1)) '........... / Résultat = 4 pour les 0

Décomposition ci-dessous

On utilise MOD pour reproduire un pattern régulier sur une plage de cellules,

comme une expression régulière (regex) mais appliquée aux colonnes.

Le pattern

Chaque groupe contient 3 cellules :

[A , B , C] ' On veut travailler uniquement sur la cellule B de chaque groupe.

Deux conditions dans la formule

Condition 1 :

la cellule doit contenir la valeur recherchée (ex : 0) dans la cellule B de chaque groupe.

Condition 2 :

la cellule doit être la 2ᵉ cellule de chaque groupe de 3

MOD(COLONNE(plage) - COLONNE(première cellule); 3) = 1

Pourquoi COLONNE(plage) - COLONNE(première cellule)

COLONNE(A3:AC3) donne des indices Excel : Excel Base 1

1,2,3,4,...,29

Mais pour appliquer un pattern régulier, on travaille en base 0 (Mémoire machine) :

0,1,2,3,...,28

Donc on écrit :

COLONNE(A3:AC3) - COLONNE(A3)

Pourquoi MOD(...;3)

MOD(x;3) donne la position de chaque cellule dans un groupe de 3 :

0,1,2 | 0,1,2 | 0,1,2 | ...

Groupes :

Avant MOD :

Groupe 1 : 0 1 2

Groupe 2 : 3 4 5

Groupe 3 : 6 7 8

Après MOD :

0 1 2 | 0 1 2 | 0 1 2 | ...

Pourquoi = 1

Parce que dans un groupe [0,1,2],

la position 1 correspond à la 2ᵉ cellule (B) du groupe : référence a en base 0 (mémoire machine)

~~0 = A ← Exclus de SOMMEPROD~~

1 = B ← celle qu’on garde (si elle contiennent 0) on fait la somme avec SOMMEPROD

~~2 = C ← Exclus de SOMMEPROD~~

Donc :

MOD(...;3)= 1 pour rappel [A , B , C] ' On veut travailler uniquement sur la cellule B de chaque groupe.

Logique : [A , B , C] | [D , E , F] | [G , H , I] | Etc.

sélectionne uniquement les cellules B, E, H, K, Etc.

Rôle de SOMMEPROD

SOMMEPROD combine les deux conditions :

la cellule est à la bonne position dans le pattern soit B, E, H, K, Etc.

la cellule contient la valeur recherchée soit 0

Et additionne les cas où les deux sont vrais.

XL 2021 calculer nombre de cellules contant 1 dans colonnes non contigües

DUMART

XLDnaute Nouveau

Saboh12617

XLDnaute Junior

Cousinhub

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

DUMART

XLDnaute Nouveau

DUMART

XLDnaute Nouveau

job75

XLDnaute Barbatruc

DUMART

XLDnaute Nouveau

DUMART

XLDnaute Nouveau

job75

XLDnaute Barbatruc

laurent950

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

laurent950

XLDnaute Barbatruc

Discussions similaires

XL 2021 calculer nombre de cellules contant 1 dans colonnes non contigües

XLDnaute Nouveau

XLDnaute Junior

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

Discussions similaires

Privacy & Transparency

Privacy & Transparency