Êtes-vous curieux de savoir s’il est possible d’inclure plusieurs colonnes de somme dans la fonction SUMIF dans Google Sheets? Habituellement, cela n’est pas possible, mais j’ai une astuce pour utiliser plusieurs colonnes de somme dans la fonction Sumif. Cette astuce fonctionne également sous forme de tableau.
Lorsque vous essayez d’inclure plusieurs colonnes de somme dans Sumif dans Google Sheets, vous obtiendrez une erreur #N/A ou la somme de la première colonne uniquement. Voyons un exemple.
Données d’exemple:
| Description | Jan | Fév |
|---|---|---|
| Lemon | 100 | 200 |
| Orange | 300 | 400 |
| Lemon | 200 | 300 |
| Grapefruit | 400 | 500 |
La formule suivante renverrait 600, c’est-à-dire la somme totale de « Lemon » dans la colonne B.
=sumif(A2:A5,"Lemon",B2:C5)
Si vous utilisez la formule suivante, vous obtiendrez une erreur #N/A! en raison du mauvais nombre d’arguments utilisés.
=sumif(A2:A5,"Lemon",B2:B5,C2:C5)
Cela signifie généralement que la fonction SUMIF ne peut gérer qu’une seule plage de somme. Mais il existe une astuce intelligente pour contourner cette limitation.
Comment inclure plusieurs colonnes de somme dans la fonction Sumif dans Google Sheets
Voici l’astuce pour inclure plusieurs colonnes de somme dans la fonction Sumif dans Google Sheets. À ma connaissance, cela ne fonctionne pas dans Excel.
Syntaxe: SUMIF(plage, critère, [plage_somme])
Étapes:
- Trouvez le nombre de colonnes à additionner.
- Dupliquez le paramètre ‘plage’ selon le nombre de colonnes de somme. Si vous avez 2 colonnes, dupliquez la plage 2 fois (Syntaxe – {plage, plage,…}).
- Ensuite, utilisez plusieurs colonnes de somme en tant que plage_somme.
Exemple:
Selon nos données ci-dessus, la plage est A2:A5 et le nombre de colonnes à additionner est de deux. Nous devons donc dupliquer la plage A2:A5 deux fois. Comment faire ?
Voici comment :
{A2:A5,A2:A5}
Maintenant, voyons la formule Sumif.
=sumif({A2:A5,A2:A5},"Lemon",B2:C5)
Cela renverra 900 en sortie.
Oui! C’est l’astuce intelligente pour inclure deux colonnes de somme dans la fonction Sumif dans Google Sheets. Cela fonctionnera également avec la formule Array (critères multiples) Sumif.
Exemple:
=ArrayFormula(sumif({A2:A5,A2:A5},{"Lemon";"Avocado"},B2:C5))
Si vous avez une autre colonne, par exemple la colonne D, la formule serait :
=sumif({A2:A5,A2:A5,A2:A5},"Lemon",B2:D5)
Si vous avez plusieurs colonnes de somme dans la plage_somme de Sumif, il ne sera pas pratique de dupliquer la plage. Dans ce cas, nous pouvons simplifier la formule comme suit.
Je vais réécrire la formule précédente avec une formule de plage dynamique.
=ArrayFormula(sumif(if(len(B2:D5)>=0,A2:A5),"Lemon",B2:D5))
- plage :
if(len(B2:D5)>=0,A2:A5) - critère : « Lemon »
- plage_somme : B2:D5
Il existe également plusieurs astuces intelligentes/alternatives. Pour en savoir plus, utilisons les données d’exemple sur la capture d’écran ci-dessous (seules les 3 premières colonnes).
Astuces qui impliquent Sumif
Sur l’image, vous pouvez voir les résultats de cinq formules dans les cellules D2:H2 et les descriptions des formules dans les cellules D1:H1.
Les cinq formules additionnent les colonnes B et C si le nom des jours de la semaine dans la colonne A est « Dimanche ». Cela signifie que le critère est « Dimanche ».
Si vous êtes un utilisateur de base de Google Sheets et que vous souhaitez uniquement utiliser SUMIF, vous pouvez suivre la solution basée sur la colonne d’aide ci-dessous. Cela vous serait facile à comprendre.
Il est facile d’utiliser SUMIF avec une colonne d’aide pour inclure plusieurs colonnes de somme.
Je commencerai par la solution basée sur la colonne d’aide, puis vous pourrez voir cinq autres formules différentes. Choisissez celle qui vous convient le mieux.
Sumif avec colonne d’aide
Exemple:
=sumif(A2:A13,"Dimanche",D2:D13)
J’ai utilisé la colonne D, qui est la colonne d’aide, comme plage_somme dans SUMIF. La colonne D contient la somme des colonnes B et C.
Je sais que la plupart d’entre vous ne sont pas favorables à l’utilisation d’une colonne d’aide. Moi-même, j’utilise très rarement des colonnes d’aide. Voici donc une solution alternative utilisant deux formules Sumif.
En additionnant les résultats de plusieurs formules Sumif
Je ne pense pas que vous ayez besoin d’explications. Dans l’exemple ci-dessous, la première formule SUMIF additionne la colonne B. La deuxième formule additionne la colonne C.
Formule:
=sumif(A2:A13,"Dimanche",B2:B13)+sumif(A2:A13,"Dimanche",C2:C13)
Formules alternatives à SUMIF avec plusieurs colonnes de somme
Voici les formules alternatives les plus utiles si vous souhaitez utiliser plusieurs colonnes de somme dans Sumif dans Google Sheets.
Alternative de la fonction SUM et IF à Sumif avec plusieurs colonnes de somme
Oui! Cette fois, j’utilise la fonction SUM et IF logique pour remplacer SUMIF. C’est l’une des formules les plus utilisées dans Excel.
=ArrayFormula( SUM( if(A2:A13="Dimanche",B2:B13+C2:C13) ) )
Mais dans Excel, au lieu de la fonction ArrayFormula, vous devez entrer la formule en utilisant Ctrl+Shift+Entrée.
Vous aimerez: Formule matricielle : comment elle diffère dans Google Sheets et Excel.
Il existe encore de meilleures alternatives à SUMIF avec plusieurs colonnes de somme dans Google Sheets. Lisez la suite.
Alternative à SUMIF avec Query dans Google Sheets
Comme je l’ai dit à plusieurs reprises par le passé, Query est l’une des meilleures fonctions dans Google Doc Sheets. Vous pouvez utiliser Query comme substitut à de nombreuses autres fonctions dans Google Sheets.
Voyez comment Query remplace SUMIF dans ce cas. C’est l’une des solutions élégantes.
=sum( query( A1:C,"Select B+C where A='Dimanche'" ) )
Combinaison de filtre et de somme – Recommandé ✓
Il s’agit de l’une des meilleures et de ma recommandation en tant qu’alternative à Sumif avec plusieurs colonnes de somme dans Google Sheets.
Formule:
=sum( filter(B2:C13,A2:A13="Dimanche") )
La formule ci-dessus utilise la fonction Filter pour filtrer les colonnes B et C (plage_somme) en fonction du critère, c’est-à-dire « Dimanche », puis additionne les colonnes.
Maintenant, voici la solution finale.
Sumproduct – Recommandé ✓
Je sais que de nombreux utilisateurs de Google Sheets utilisent fréquemment SUMPRODUCT pour remplacer SUMIF/SUMIFS.
La raison en est que SUMPRODUCT peut rendre vos formules plus courtes, plus intelligentes et plus faciles à lire. Regardez celle-ci !
Formule:
=SUMPRODUCT(B2:C*(A2:A="Dimanche"))
J’ai fourni plusieurs options de formules ci-dessus pour vous permettre d’utiliser plusieurs colonnes de somme dans Sumif dans Google Sheets.
Laquelle préférez-vous ? J’aimerais connaître vos opinions dans les commentaires ci-dessous. Amusez-vous bien !
Lecture supplémentaire :
- Comment utiliser SUMIFS pour additionner plusieurs colonnes dans Google Sheets
- Comment utiliser la fonction Sumif horizontalement dans Google Sheets
- Somme des lignes ou des colonnes de la matrice en utilisant Sumif dans Google Sheets
