Aquest tutorial ofereix una demostració detallada de com utilitzar les funcions SUMIF i SUMIFS a Google Sheets amb fórmules i exemples.
SUMIF és una de les funcions matemàtiques de Google Sheets, que s'utilitza per sumar cel·les condicionalment. Bàsicament, la funció SUMIF cerca una condició específica en un rang de cel·les i després suma els valors que compleixen la condició donada.
Per exemple, teniu una llista de despeses als fulls de Google i només voleu resumir les despeses que superin un determinat valor màxim. O teniu una llista d'articles de comanda i els seus imports corresponents i només voleu saber l'import total de la comanda d'un article específic. Aquí és on la funció SUMIF és útil.
SUMIF es pot utilitzar per sumar valors basats en la condició del número, la condició del text, la condició de la data, els comodins, així com en funció de cel·les buides i no buides. Fulls de càlcul de Google té dues funcions per resumir valors segons criteris: SUMIF i SUMIFS. La funció SUMIF suma nombres basats en una condició mentre que SUMIFS suma nombres basats en diverses condicions.
En aquest tutorial, explicarem com utilitzar les funcions SUMIF i SUMIFS a Google Sheets per sumar nombres que compleixen una determinada condició.
Funció SUMIF a Google Sheets: sintaxi i arguments
La funció SUMIF és només una combinació de la funció SUMA i SI. La funció SI explora l'interval de cel·les per a una condició determinada i, a continuació, la funció SUMA suma els nombres corresponents a les cel·les que compleixen la condició.
Sintaxi de la funció SUMIF:
La sintaxi de la funció SUMIF a Google Sheets és la següent:
=SUMIF(interval, criteris, [interval_sum])Arguments:
rang - El rang de cel·les on busquem les cel·les que compleixen els criteris.
criteris – Els criteris que determinen quines cel·les cal afegir. Podeu basar el criteri en el número, cadena de text, data, referència de cel·la, expressió, operador lògic, caràcter comodí i altres funcions.
rang_sum – Aquest argument és opcional. És l'interval de dades amb valors a sumar si l'entrada d'interval corresponent coincideix amb la condició. Si no incloeu aquest argument, llavors el "interval" es suma.
Ara, vegem com utilitzar la funció SUMIF per sumar valors amb criteris diferents.
Funció SUMIF amb criteris numèrics
Podeu sumar nombres que compleixin determinats criteris en un rang de cel·les utilitzant un dels operadors de comparació següents per fer criteris.
- més gran que (>)
- menys de (<)
- superior o igual a (>=)
- inferior o igual a (<=)
- igual a (=)
- no és igual a ()
Suposem que tens el següent full de càlcul i que t'interessen les vendes totals de 1.000 o més.
A continuació s'explica com podeu introduir la funció SUMIF:
Primer, seleccioneu la cel·la on voleu que aparegui la sortida de la suma (D3). Per resumir els nombres de B2:B12 que siguin majors o iguals a 1000, escriviu aquesta fórmula i premeu "Enter":
=SUMIF(B2:B12,">=1000",B2:B12)En aquesta fórmula d'exemple, els arguments interval i sum_range (B2:B12) són els mateixos, perquè els números de vendes i els criteris s'apliquen al mateix interval. I hem introduït el número abans de l'operador de comparació i l'hem inclòs entre cometes perquè els criteris sempre s'han de tancar entre cometes dobles, excepte per a una referència de cel·la.
La fórmula va buscar números que són superiors o iguals a 1000 i després va sumar tots els valors coincidents i va mostrar el resultat a la cel·la D3.
Com que els arguments rang i sum_range són els mateixos, podeu aconseguir el mateix resultat sense els arguments sum_range a la fórmula, com aquesta:
=SUMIF(B2:B12,">=1000")
O podeu proporcionar la referència de cel·la (D2) que conté el nombre en lloc dels criteris de nombre i unir l'operador de comparació amb aquesta referència de cel·la a l'argument criteri:
=SUMIF(B2:B12,">="&D2)Com podeu veure, l'operador de comparació encara s'introdueix entre cometes dobles i l'operador i la referència de la cel·la estan concatenats per un ampersand (&). I no cal incloure la referència de la cel·la entre cometes.
Nota: Quan feu referència a la cel·la que conté criteris, assegureu-vos de no deixar cap espai inicial o final al valor de la cel·la. Si el vostre valor té algun espai innecessari abans o després del valor de la cel·la referida, la fórmula tornarà "0" com a resultat.
També podeu utilitzar altres operadors lògics de la mateixa manera per fer condicions a l'argument criteri. Per exemple, per sumar valors inferiors a 500:
=SUMIF(B2:B12,"<500")
Suma si nombres iguals a
Si voleu afegir nombres iguals a un nombre determinat, podeu introduir només el número o bé introduir el nombre amb el signe igual a l'argument del criteri.
Per exemple, per sumar les quantitats de vendes corresponents (columna B) per a quantitats (columna C) els valors de les quals siguin iguals a 20, proveu qualsevol d'aquestes fórmules:
=SUMIF(C2:C12;"=20",B2:B12)
=SUMIF(C2:C12;"20", B2:B12)
=SUMIF(C2:C12;E2;B2:B12)
Per sumar nombres de la columna B amb una quantitat no igual a 20 a la columna C, proveu aquesta fórmula:
=SUMIF(C2:C12;"20", B2:B12)
Funció SUMIF amb criteris de text
Si voleu sumar números en un rang de cel·les (columna o fila) corresponents a les cel·les que tenen un text específic, simplement podeu incloure aquest text o la cel·la que conté el text a l'argument de criteri de la vostra fórmula SUMIF. Tingueu en compte que la cadena de text sempre s'ha d'incloure entre cometes dobles (" ").
Per exemple, si voleu la quantitat total de vendes a la regió "Oest", podeu utilitzar la fórmula següent:
=SUMIF(C2:C13;"Oest", B2:B13)En aquesta fórmula, la funció SUMIF cerca el valor "Oest" a l'interval de cel·les C2:C13 i suma el valor de vendes corresponent a la columna B. A continuació, mostra el resultat a la cel·la E3.
També podeu fer referència a la cel·la que conté text en lloc d'utilitzar el text a l'argument criteri:
=SUMIF(C2:C12;E2;B2:B12)
Ara, anem a obtenir els ingressos totals de totes les regions excepte "Oest". Per fer-ho, utilitzarem no igual a l'operador () a la fórmula:
=SUMIF(C2:C12,""&E2,B2:B12)
SUMIF amb comodins
En el mètode anterior, la funció SUMIF amb criteris de text comprova l'interval amb el text especificat exactament. A continuació, suma els números parrells al text exacte i ignora tots els altres números, inclosa la cadena de text parcialment coincident. Per sumar els nombres amb cadenes de text que coincideixen parcialment, heu d'adaptar un dels següents caràcters comodins als vostres criteris:
?(signe d'interrogació) s'utilitza per fer coincidir qualsevol caràcter, en qualsevol lloc de la cadena de text.*(asterisc) s'utilitza per trobar paraules coincidents juntament amb qualsevol seqüència de caràcters.~(tilde) s'utilitza per fer coincidir textos amb un signe d'interrogació (?) o un asterisc (*).
Farem aquest exemple de full de càlcul per als productes i les seves quantitats per sumar números amb comodins:
Asterisc (*) Comodí
Per exemple, si voleu sumar les quantitats de tots els productes Apple, utilitzeu aquesta fórmula:
=SUMIF(A2:A14;"Apple*", B2:B14)Aquesta fórmula SUMIF troba tots els productes amb la paraula "Apple" al principi i qualsevol nombre de caràcters després (indicat per '*'). Un cop trobada la coincidència, resumeix la Quantitat números corresponents a les cadenes de text coincidents.
També és possible utilitzar diversos comodins als criteris. I també podeu introduir caràcters comodís amb referències de cel·les en lloc de text directe.
Per fer-ho, els comodins s'han d'entregar entre cometes dobles (" ") i concatenar-los amb les referències de cel·la:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Aquesta fórmula suma les quantitats de tots els productes que porten la paraula 'Redmi', sense importar on es trobi la paraula a la cadena.
Signe d'interrogació (?) Comodí
Podeu utilitzar el comodí del signe d'interrogació (?) per fer coincidir les cadenes de text amb qualsevol caràcter.
Per exemple, si voleu trobar quantitats de totes les variants de Xiaomi Redmi 9, podeu utilitzar aquesta fórmula:
=SUMIF(A2:A14,"Xiaomi Redmi 9?", B2:B14)La fórmula anterior cerca cadenes de text amb la paraula "Xiaomi Redmi 9" seguida de qualsevol caràcter i suma el corresponent Quantitat nombres.
Tilde (~) Comodí
Si voleu fer coincidir un signe d'interrogació (?) o un asterisc (*) real, inseriu el caràcter tilde (~) abans del comodí a la part de condició de la fórmula.
Per afegir les quantitats de la columna B amb la cadena corresponent que tenen un asterisc al final, introduïu la fórmula següent:
=SUMIF(A2:A14,"Samsung Galaxy V~*", B2:B14)
Per afegir quantitats a la columna B que tinguin un signe d'interrogació (?) a la columna A de la mateixa fila, proveu la fórmula següent:
=SUMIF(A2:A14,"~?",B2:B14)
Funció SUMIF amb criteris de data
La funció SUMIF també us pot ajudar a sumar condicionalment els valors basats en criteris de data, per exemple, números corresponents a una data determinada, o abans d'una data o després d'una data. També podeu utilitzar qualsevol dels operadors de comparació amb un valor de data per crear criteris de data per sumar números.
La data s'ha d'introduir en el format de data compatible amb els fulls de càlcul de Google, o com a referència de cel·la que contingui una data, o bé utilitzant una funció de data com DATE() o TODAY().
Utilitzarem aquest full de càlcul d'exemple per mostrar-vos com funciona la funció SUMIF amb criteris de data:
Suposem que voleu sumar els imports de vendes que es van produir el 29 de novembre de 2019 o abans (<=) al conjunt de dades anterior, podeu afegir aquests números de vendes mitjançant la funció SUMIF d'una d'aquestes maneres:
=SUMIF(C2:C13,"<=29 de novembre de 2019",B2:B13)La fórmula anterior comprova cada cel·la de C2 a C13 i només coincideix amb aquelles cel·les que contenen dates anteriors al 29 de novembre de 2019 (29/11/2019). A continuació, suma l'import de les vendes corresponent a les cel·les coincidents de l'interval de cel·les B2:B13 i mostra el resultat a les cel·les E3.
La data es pot proporcionar a la fórmula en qualsevol format que reconegui Google Sheets, com ara "29 de novembre de 2019", "29 de novembre de 2019", o "29/11/2019", etc. Recordeu el valor de la data i l'operador ha de sempre anar entre cometes dobles.
També podeu utilitzar la funció DATE() als criteris en lloc de dir el valor de la data:
=SUMIF(C2:C13,"<="&DATA(2019,11,29),B2:B13)
O bé, podeu utilitzar la referència de cel·la en lloc de la data a la part de criteris de la fórmula:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Si voleu sumar els imports de les vendes en funció de la data d'avui, podeu utilitzar la funció AVUI () a l'argument criteri.
Per exemple, per sumar tots els imports de les vendes per a la data d'avui, utilitzeu aquesta fórmula:
=SUMIF(C2:C13, AVUI(),B2:B13)
Funció SUMIF amb cel·les en blanc o no en blanc
De vegades, és possible que hàgiu de sumar els nombres d'un rang de cel·les amb cel·les en blanc o no en blanc a la mateixa fila. En aquests casos, podeu utilitzar la funció SUMIF per sumar valors en funció de criteris on les cel·les estiguin buides o no.
Suma si en blanc
Hi ha dos criteris a Google Sheets per trobar cel·les en blanc: "" o "=".
Per exemple, si voleu sumar tota la quantitat de vendes que contenen cadenes de longitud zero (visualment sembla buida) a la columna C, utilitzeu cometes dobles sense espai entremig a la fórmula:
=SUMIF(C2:C13,"",B2:B13)
Per sumar tot l'import de les vendes a la columna B amb cel·les en blanc completes a la columna C, incloeu "=" com a criteri:
=SUMIF(C2:C13,"=",B2:B13)
Suma si no està en blanc:
Si voleu sumar cel·les que contenen qualsevol valor (no buit), podeu utilitzar "" com a criteri a la fórmula:
Per exemple, per obtenir la quantitat total de vendes amb qualsevol data, utilitzeu aquesta fórmula:
=SUMIF(C2:C13,"",B2:B13)
SUMIF Basat en criteris múltiples amb lògica OR
Com hem vist fins ara, la funció SUMIF està dissenyada per sumar nombres en funció d'un sol criteri, però és possible sumar valors en funció de diversos criteris amb la funció SUMIF a Google Sheets. Es pot fer unint més d'una funció SUMIF en una sola fórmula amb lògica OR.
Per exemple, si voleu sumar l'import de les vendes a la regió "Oest" o a la regió "Sud" (lògica O) a l'interval especificat (B2:B13), utilitzeu aquesta fórmula:
=SUMIF(C2:C13;"Oest", B2:B13)+SUMIF(C2:C13;"Sud", B2:B13)Aquesta fórmula suma les cel·les quan almenys una de les condicions és TRUE. Per tant, es coneix com a "lògica OR". També sumarà els valors quan es compleixin totes les condicions.
La primera part de la fórmula comprova l'interval C2:C13 per al text "Oest" i suma els valors de l'interval B2:B13 quan es compleix la coincidència. La part dels segons de les comprovacions del valor de text "Sud" al mateix rang C2:C13 i després suma els valors amb el text coincident al mateix rang de suma B2:B13. A continuació, les dues sumes se sumen i es mostren a la cel·la E3.
En els casos en què només es compleix un criteri, només retornarà aquest valor de suma.
També podeu utilitzar diversos criteris en lloc d'un o dos. I si utilitzeu diversos criteris, és millor utilitzar una referència de cel·la com a criteri en lloc d'escriure el valor directe a la fórmula.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
SUMIF amb lògica OR afegeix valors quan es compleix almenys un dels criteris especificats, però si només voleu sumar valors només quan es compleixen totes les condicions especificades, heu d'utilitzar la seva nova funció germà SUMIFS().
Funció SUMIFS a Fulls de Google (criteris múltiples)
Quan utilitzeu la funció SUMIF per sumar valors en funció de diversos criteris, la fórmula pot ser massa llarga i complicada i és propens a cometre errors. A més, SUMIF us permetrà sumar valors només en un únic rang i quan qualsevol de les condicions sigui TRUE. Aquí és on entra la funció SUMIFS.
La funció SUMIFS us ajuda a sumar valors en funció de diversos criteris de concordança en un o més intervals. I funciona amb la lògica AND, és a dir, només pot sumar valors quan es compleixen totes les condicions donades. Fins i tot si una condició és falsa, retornarà "0" com a resultat.
Sintaxi i arguments de la funció SUMIFS
La sintaxi de la funció SUMIFS és la següent:
=SUMIFS(interval_suma, rang_criteri1, criteri1, [interval_criteri2, ...], [criteri2, ...])On,
- rang_sum - L'interval de cel·les que contenen els valors que voleu sumar quan es compleixin totes les condicions.
- interval_criteri1 - És l'interval de cel·les on comproveu els criteris1.
- criteri 1- És la condició que heu de comprovar amb criteria_range1.
- criteria_range2, criteri2,...– Els rangs i criteris addicionals a avaluar. I podeu afegir més intervals i condicions a la fórmula.
Utilitzarem el conjunt de dades a la captura de pantalla següent per demostrar com funciona la funció SUMIFS amb diferents criteris.
SUMIFS amb condicions de text
Podeu sumar valors en funció de dos criteris de text diferents en diferents intervals. Per exemple, suposem que voleu esbrinar l'import total de les vendes de l'article de tenda lliurat. Per a això, utilitzeu aquesta fórmula:
=SUMIFS(D2:D13,A2:A13,"Tenda", C2:C13,"Entregat")En aquesta fórmula, tenim dos criteris: "Tenda" i "Lliurat". La funció SUMIFS cerca l'element "Tenda" (criteri1) a l'interval A2:A13 (interval_criteri1) i comprova l'estat "Enviat" (criteri2) a l'interval C2:C13 (interval_criteri2). Quan es compleixen ambdues condicions, suma el valor corresponent a l'interval de cel·les D2:D13 (interval_sum).
SUMIFS amb criteris numèrics i operadors lògics
Podeu utilitzar operadors condicionals per crear condicions amb números per a la funció SUMIFS.
Per trobar les vendes totals de més de 5 quantitats de qualsevol article a l'estat de Califòrnia (CA), utilitzeu aquesta fórmula:
=SUMIFS(E2:E13;D2:D13;">5",B2:B13;"CA")Aquesta fórmula té dues condicions: ">5" i "CA".
Aquesta fórmula verifica les quantitats (Quantitat) superiors a 5 en el rang D2:D13 i comprova l'estat "CA" en el rang B2:B13. I quan es compleixen ambdues condicions (és a dir, n'hi ha a la mateixa fila), suma la quantitat en E2:E13.
SUMIFS amb criteris de data
La funció SUMIFS també us permet comprovar diverses condicions en el mateix rang així com diferents rangs.
Suposem que voleu comprovar l'import total de les vendes dels articles lliurats després del 31/5/2021 i abans del 10/6/2021 i, a continuació, utilitzeu aquesta fórmula:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)La fórmula anterior té tres condicions: 31/5/2021, 10/5/2021 i Entregada. En lloc d'utilitzar valors directes de data i text, ens hem referit a cel·les que contenen aquests criteris.
La fórmula verifica les dates posteriors al 31/5/2021 (G1) i les dates anteriors al 6/10/2021 (G2) en el mateix interval D2:D13 i verifica l'estat "Entregat" entre aquestes dues dates. A continuació, suma la quantitat relacionada en l'interval E2:E13.
SUMIFS amb cel·les en blanc i no en blanc
De vegades, és possible que vulgueu trobar la suma de valors quan una cel·la corresponent està buida o no. Per fer-ho, podeu utilitzar un dels tres criteris que hem comentat abans: "=", "" i "".
Per exemple, si només voleu sumar la quantitat d'articles de "tenda" per als quals encara no s'ha confirmat la data de lliurament (cel·les buides), podeu utilitzar els criteris de "=":
=SUMIFS(D2:D13,A2:A13,"Tenda",C2:C13,"=")La fórmula cerca l'element "Tenda" (criteri1) a la columna A amb les cel·les en blanc corresponents (criteri2) a la columna C i després suma la quantitat corresponent a la columna D. El "=" representa una cel·la completament en blanc.
Per trobar la suma d'articles de "tenda" per als quals s'ha confirmat la data de lliurament (no les cel·les buides), utilitzeu "" com a criteri:
=SUMIFS(D2:D13;A2:A13;"Tenda", C2:C13,"")Acabem de canviar "=" per "" en aquesta fórmula. Troba la suma dels elements de la tenda amb cel·les no en blanc a la columna C.
SUMIFS amb lògica OR
Com que la funció SUMIFS funciona amb la lògica AND, només suma quan es compleixen totes les condicions. Però, què passa si voleu sumar el valor en funció de diversos criteris quan es compleix algun dels criteris? El truc és utilitzar diverses funcions SUMIFS.
Per exemple, si voleu sumar l'import de les vendes per a "Portabicicletes" O "Motxilla" quan el seu estat és "Encarregat", proveu aquesta fórmula:
=SUMIFS(D2:D13,A2:A13,"Portabicicletes",C2:C13,"Encarregat") +SUMIFS(D2:D13,A2:A13,"Motxilla", C2:C13,"Encarregat")La primera funció SUMIFS comprova dos criteris "Portabicicletes" i "Encarregat" i suma els valors d'import a la columna D. A continuació, la segona funció SUMIFS verifica dos criteris "Motxilla" i "Encarregat" i suma els valors de la quantitat a la columna D. I després , les dues sumes es sumen i es mostren a F3. En paraules senzilles, aquesta fórmula suma quan es demana el "portabicicletes" o la "motxilla".
Això és tot el que necessiteu saber sobre les funcions SUMIF i SUMIFS a Google Sheets.