Besoin de sommer F2:F892 uniquement quand G2:G892 « est un nombre » (positif, négatif ou 0), tout en ignorant les textes comme "Deleted" ? Voici des solutions fiables pour toutes versions d’Excel, plus des variantes 365 et des astuces anti-pièges.
Récap’ express des formules (copier/coller)
Basique, toutes versions – recommandé
=SUMPRODUCT(F2:F892, --ISNUMBER(G2:G892))ISNUMBER(G2:G892)crée un masque VRAI/FAUX par ligne.--convertit VRAI/FAUX en 1/0 pour pondérerF.
Si certains « nombres » sont stockés comme texte (ex. "123")
Important : excluez explicitement les vides pour éviter de compter "" comme 0.
=SUMPRODUCT(F2:F892, --(G2:G892<>""), --ISNUMBER(1*G2:G892))Excel 365 (formules dynamiques) – lisible
=SUM(FILTER(F2:F892, ISNUMBER(G2:G892)))365 + nombres-textes (avec exclusion des vides) :
=SUM(FILTER(F2:F892, (G2:G892<>"") * ISNUMBER(1*G2:G892)))Alternative en pur SUMIFS (sans ISNUMBER)
Encadrer l’intervalle numérique et exclure les vides :
=SUMIFS(F2:F892, G2:G892, ">-9.99E+307", G2:G892, "<9.99E+307", G2:G892, "<>"&"")Remarque locale : selon vos paramètres régionaux, remplacez les virgules par des points-virgules dans les formules.
Pourquoi ça marche ?
Le duo SUMPRODUCT + ISNUMBER
SUMPRODUCT additionne les produits terme à terme. En multipliant F2:F892 par un masque logique, seules les lignes qui vérifient le critère contribuent au total :
=SUMPRODUCT( F2:F892 * --ISNUMBER(G2:G892) )Quand G est numérique, ISNUMBER renvoie VRAI → -- le transforme en 1 → F est conservé. Si G est texte (ex. "Deleted") ou vide, ISNUMBER renvoie FAUX → 0 → la ligne est neutralisée.
Pourquoi 1*G pour les nombres-textes ?
Si une cellule affiche "123" mais est réellement du texte, ISNUMBER(G) renverra FAUX. Le truc 1*G force la conversion des « nombres-textes » en nombres réels. Les autres textes (ex. "Deleted") deviennent des erreurs, que ISNUMBER filtrera. Attention : en Excel, 1*"" renvoie 0 (donc un nombre) ; il faut aussi exclure les vides via G<>"" pour ne pas additionner les lignes vides par mégarde.
Pourquoi la variante SUMIFS fonctionne sans ISNUMBER ?
SUMIFS ne sait pas tester « est numérique ». À la place, on encadre G par un intervalle immense (>-9.99E+307 et <9.99E+307) qui capture tous les nombres possibles (négatifs, 0, positifs) et on exclut les vides (<>""). Les textes ne satisfont pas simultanément des critères numériques : ils sont donc ignorés.
Jeu d’essai pas-à-pas
Petit tableau pédagogique (lignes 2→11) :
| Ligne | F (montant) | G (critère) | Type de G | Pris par ISNUMBER(G) | Pris par (G<>"")*ISNUMBER(1*G) |
|---|---|---|---|---|---|
| 2 | 100 | 7 | Nombre | ✔ | ✔ |
| 3 | 50 | Deleted | Texte | ✖ | ✖ |
| 4 | -30 | (vide) | Vide | ✖ | ✖ |
| 5 | 25 | « 123 » | Nombre stocké comme texte | ✖ | ✔ |
| 6 | 40 | -2 | Nombre (négatif) | ✔ | ✔ |
| 7 | 10 | ok | Texte | ✖ | ✖ |
| 8 | 5 | 0 | Nombre (zéro) | ✔ | ✔ |
| 9 | 60 | » 3 « | Nombre-texte avec espaces | ✖ | ✔ |
| 10 | -20 | N/A | Texte | ✖ | ✖ |
| 11 | 15 | 4.5 | Nombre (décimal) | ✔ | ✔ |
Résultats attendus
| Formule | Lignes comptées | Total obtenu |
|---|---|---|
=SUMPRODUCT(F2:F892, --ISNUMBER(G2:G892)) | 2, 6, 8, 11 | 160 |
=SUMPRODUCT(F2:F892, --(G2:G892<>""), --ISNUMBER(1*G2:G892)) | 2, 5, 6, 8, 9, 11 | 245 |
Variantes fréquentes (à adapter en 1 minute)
Inclure un seuil, ex. « G numérique ET > 0 »
=SUMPRODUCT(F2:F892, --ISNUMBER(G2:G892), --(G2:G892>0))Avec nombres-textes possibles :
=SUMPRODUCT(F2:F892, --(G2:G892<>""), --ISNUMBER(1*G2:G892), --(1*G2:G892>0))Tout-en-un : additionner si « G est numérique » ou « G = « Deleted » »
Si vous souhaitez regrouper deux règles (au lieu de faire deux sommes séparées) :
=SUMPRODUCT(
F2:F892,
--( ((G2:G892="Deleted") + ((G2:G892<>"") * ISNUMBER(1*G2:G892))) > 0 )
)Explication : on additionne deux tests logiques pour simuler un « OU » ; s’il y a au moins une condition vraie, on applique le masque.
Ignorer les erreurs dans F
Si F contient parfois #N/A, utilisez IFERROR pour protéger la somme :
=SUMPRODUCT(IFERROR(F2:F892,0), --ISNUMBER(G2:G892))Version robuste nombres-textes + vides exclus :
=SUMPRODUCT(IFERROR(F2:F892,0), --(G2:G892<>""), --ISNUMBER(1*G2:G892))Excel 365 : formule encore plus lisible avec LET
=LET(
montant, F2:F892,
crit, G2:G892,
SUM(FILTER(montant, (crit<>"") * ISNUMBER(1*crit)))
)Tableaux structurés (références nommées)
Supposons un tableau Excel nommé T_Data avec colonnes [Montant] (F) et [Critere] (G) :
=SUMPRODUCT(T_Data[Montant], --ISNUMBER(T_Data[Critere]))Ou en 365 :
=SUM(FILTER(T_Data[Montant], ISNUMBER(T_Data[Critere])))Pièges courants & comment les éviter
- Cellules vides : avec le truc
1*G,""devient 0 (et donc « numérique »). Ajoutez toujours(G<>"")pour exclure les vides. - Dates : Excel stocke les dates comme nombres.
ISNUMBERles considérera « numériques ». Si vous devez les exclure, filtrez par format ou par plage de dates (ex.<DATE(1900,1,1)selon votre calendrier) – mais cela dépend de votre contexte métier. - Espaces et caractères invisibles : un nombre texte avec espaces (
" 3 ") ou un espace insécable peut échapper aux conversions. Combinez nettoyage + conversion :=SUMPRODUCT(F2:F892, --(G2:G892<>""), --ISNUMBER(1*TRIM(CLEAN(G2:G892))))(TRIMsupprime les espaces superflus,CLEANsupprime certains caractères non imprimables.) - Différence « est un nombre » vs « contient un chiffre quelque part » : les formules ci-dessus testent le type numérique de la cellule. Si vous vouliez « contient un chiffre dans un texte » (ex.
"code-123"), utilisez 365 :=SUM(FILTER(F2:F892, BYROW(G2:G892, LAMBDA(r, SUM(--ISNUMBER(SEARCH({"0","1","2","3","4","5","6","7","8","9"}, r)))>0 )))) - Performance :
SUMPRODUCTest très raisonnable jusqu’à quelques dizaines de milliers de lignes. Pour des feuilles immenses, privilégiezSUMIFS(moteur optimisé) ou passez par Power Query pour normaliser les données (conversion explicite en nombre). - Booleans : VRAI/FAUX ne sont pas « nombres » pour
ISNUMBER. Ils seront exclus, ce qui est généralement souhaité.
Compatibilité & localisation
- Toutes versions d’Excel : privilégiez
SUMPRODUCT(pas de validation matricielle Ctrl+Maj+Entrée nécessaire). - Microsoft 365 :
FILTERrend la logique très lisible, surtout combiné àLET. - Séparateurs : dans certaines configurations françaises, remplacez
,par;(ex.=SOMMEPROD(F2:F892; --ESTNUM(G2:G892))si vous utilisez les noms de fonctions français).
| Fonction (anglais) | Nom français d’Excel | Remarque |
|---|---|---|
| SUMPRODUCT | SOMMEPROD | Compatible toutes versions |
| SUMIFS | SOMME.SI.ENS | Moteur très rapide |
| ISNUMBER | ESTNUM | Test du type numérique |
| FILTER | FILTRE | Microsoft 365+ |
| IFERROR | SIERREUR | Pour neutraliser les erreurs |
FAQ minute
Q : Les zéros sont-ils pris en compte ?
R : Oui. ISNUMBER renvoie VRAI pour 0 et SUMIFS avec bornes inclut 0. Si vous voulez « strictement > 0 », ajoutez un critère --(G>0) (ou 1*G>0 si nombres-textes).
Q : Et les dates ?
R : Les dates sont des nombres en interne, donc comptées. Pour les ignorer, filtrez par format ou par plage non plausible pour vos dates (par exemple en excluant les numéros de série usuels).
Q : J’ai des espaces non visibles, la conversion échoue.
R : Nettoyez avant de tester : TRIM(CLEAN(G)), puis 1* ou VALUE. Exemple : --ISNUMBER(1*TRIM(CLEAN(G2:G892))).
Q : Puis-je faire la même chose dans Google Sheets ?
R : Oui, les formules fonctionnent pratiquement à l’identique (SUMPRODUCT, ISNUMBER, FILTER, etc.). Attention toutefois : le traitement des vides peut différer légèrement (ex. 1*"").
Q : Comment diagnostiquer les cellules problématiques dans G ?
R : Ajoutez une colonne d’audit : =IF(G2="","Vide", IF(ISNUMBER(G2),"Numérique", IF(ISNUMBER(1*TRIM(CLEAN(G2))),"Nombre-texte","Texte"))). Vous verrez immédiatement ce qui coince.
Copier-coller direct selon votre séparateur
| Contexte | Avec virgules | Avec points-virgules |
|---|---|---|
| Base (tout Excel) | =SUMPRODUCT(F2:F892, --ISNUMBER(G2:G892)) | =SOMMEPROD(F2:F892; --ESTNUM(G2:G892)) |
| Nombres-textes + vides exclus | =SUMPRODUCT(F2:F892, --(G2:G892<>""), --ISNUMBER(1*G2:G892)) | =SOMMEPROD(F2:F892; --(G2:G892<>""); --ESTNUM(1*G2:G892)) |
| 365 (lisible) | =SUM(FILTER(F2:F892, ISNUMBER(G2:G892))) | =SOMME(FILTRE(F2:F892; ESTNUM(G2:G892))) |
| SUMIFS pur | =SUMIFS(F2:F892, G2:G892, ">-9.99E+307", G2:G892, "<9.99E+307", G2:G892, "<>"&"") | =SOMME.SI.ENS(F2:F892; G2:G892; ">-9.99E+307"; G2:G892; "<9.99E+307"; G2:G892; "<>"&"") |
Résumé
- Simple et robuste :
=SUMPRODUCT(F2:F892, --ISNUMBER(G2:G892)). - Si nombres-textes :
=SUMPRODUCT(F2:F892, --(G2:G892<>""), --ISNUMBER(1*G2:G892)). - Version 365 lisible :
=SUM(FILTER(F2:F892, ISNUMBER(G2:G892)))(ou avec1*G+G<>""). - Sans
ISNUMBER:SUMIFSavec grandes bornes + exclusion des vides.
Avec ces modèles, vous couvrez 99 % des cas « sommer F si G est un nombre » — que les données soient propres, mixtes ou un peu capricieuses.