Ovaj vodič pruža detaljnu demonstraciju kako koristiti funkcije SUMIF i SUMIFS u Google tablicama s formulama i primjerima.
SUMIF je jedna od matematičkih funkcija u Google tablicama, koja se koristi za uvjetno zbrajanje ćelija. U osnovi, funkcija SUMIF traži određeni uvjet u rasponu ćelija, a zatim zbraja vrijednosti koje zadovoljavaju zadani uvjet.
Na primjer, imate popis troškova u Google tablicama i želite samo zbrojiti troškove koji su iznad određene maksimalne vrijednosti. Ili imate popis stavki narudžbe i njihove odgovarajuće količine, a želite znati samo ukupni iznos narudžbe određene stavke. Tu je zgodna funkcija SUMIF.
SUMIF se može koristiti za zbrajanje vrijednosti na temelju uvjeta broja, uvjeta teksta, uvjeta datuma, zamjenskih znakova kao i na temelju praznih i nepraznih ćelija. Google tablice imaju dvije funkcije za zbrajanje vrijednosti na temelju kriterija: SUMIF i SUMIFS. Funkcija SUMIF zbraja brojeve na temelju jednog uvjeta dok SUMIFS zbraja brojeve na temelju više uvjeta.
U ovom vodiču objasnit ćemo kako koristiti funkcije SUMIF i SUMIFS u Google tablicama za zbrajanje brojeva koji ispunjavaju određene uvjete.
Funkcija SUMIF u Google tablicama – sintaksa i argumenti
Funkcija SUMIF samo je kombinacija funkcija SUM i IF. Funkcija IF skenira raspon ćelija za dani uvjet, a zatim funkcija SUM zbraja brojeve koji odgovaraju ćelijama koje ispunjavaju uvjet.
Sintaksa funkcije SUMIF:
Sintaksa funkcije SUMIF u Google tablicama je sljedeća:
=SUMIF(raspon, kriterij, [raspon_zbira])Argumenti:
raspon – Raspon ćelija u kojem tražimo ćelije koje zadovoljavaju kriterije.
kriterijima – Kriteriji koji određuju koje ćelije treba dodati. Kriterij možete temeljiti na broju, tekstualnom nizu, datumu, referenci ćelije, izrazu, logičkom operatoru, zamjenskom znaku kao i drugim funkcijama.
raspon_zbira – Ovaj argument nije obavezan. To je raspon podataka s vrijednostima za zbrajanje ako se odgovarajući unos raspona podudara s uvjetom. Ako ne uključite ovaj argument, umjesto toga se zbraja 'raspon'.
Pogledajmo sada kako upotrijebiti funkciju SUMIF za zbrajanje vrijednosti s različitim kriterijima.
Funkcija SUMIF s brojčanim kriterijima
Možete zbrojiti brojeve koji zadovoljavaju određene kriterije u rasponu ćelija korištenjem jednog od sljedećih operatora usporedbe za izradu kriterija.
- veći od (>)
- manje od (<)
- veće ili jednako (>=)
- manje ili jednako (<=)
- jednako (=)
- nije jednako ()
Pretpostavimo da imate sljedeću proračunsku tablicu i da vas zanima ukupna prodaja koja iznosi 1000 ili više.
Evo kako možete unijeti funkciju SUMIF:
Najprije odaberite ćeliju u kojoj želite da se pojavi rezultat zbroja (D3). Da biste zbrojili brojeve u B2:B12 koji su veći ili jednaki 1000, upišite ovu formulu i pritisnite 'Enter':
=SUMIF(B2:B12,">=1000",B2:B12)U ovom primjeru formule, argumenti raspon i zbroj_raspon (B2:B12) su isti, jer se brojevi prodaje i kriteriji primjenjuju na isti raspon. I upisali smo broj prije operatora za usporedbu i stavili ga u navodnike jer kriteriji uvijek trebaju biti stavljeni u dvostruke navodnike osim za referencu na ćeliju.
Formula je tražila brojeve koji su veći ili jednaki 1000, a zatim zbrojila sve podudarne vrijednosti i pokazala rezultat u ćeliji D3.
Budući da su argumenti raspon i sum_range isti, možete postići isti rezultat bez argumenata raspon_suma u formuli, na sljedeći način:
=SUMIF(B2:B12,">=1000")
Ili možete navesti referencu ćelije (D2) koja sadrži broj umjesto kriterija broja i pridružiti operatoru usporedbe s tom referencom ćelije u argumentu kriterija:
=SUMIF(B2:B12,">="&D2)Kao što možete vidjeti, operator usporedbe se i dalje unosi u dvostrukim navodnicima, a operator i referenca ćelije povezani su znakom &. I ne morate stavljati referencu ćelije u navodnike.
Bilješka: Kada upućujete na ćeliju koja sadrži kriterije, pazite da ne ostavite nikakav vodeći ili završni razmak u vrijednosti u ćeliji. Ako vaša vrijednost ima nepotreban prostor prije ili iza vrijednosti u preporučenoj ćeliji, formula će kao rezultat vratiti '0'.
Također možete koristiti druge logičke operatore na isti način za postavljanje uvjeta u argument kriterija. Na primjer, da se zbroje vrijednosti manje od 500:
=SUMIF(B2:B12,"<500")
Zbroj ako su brojevi jednaki
Ako želite zbrojiti brojeve koji su jednaki određenom broju, možete unijeti samo broj ili unijeti broj sa predznakom jednakosti u argument kriterija.
Na primjer, da zbrojite odgovarajuće iznose prodaje (stupac B) za količine (stupac C) čije su vrijednosti jednake 20, pokušajte bilo koju od ovih formula:
=SUMIF(C2:C12,"=20",B2:B12)
=SUMIF(C2:C12,"20",B2:B12)
=SUMIF(C2:C12,E2,B2:B12)
Da biste zbrojili brojeve u stupcu B s količinom koja nije jednaka 20 u stupcu C, pokušajte ovu formulu:
=SUMIF(C2:C12,"20",B2:B12)
Funkcija SUMIF s tekstualnim kriterijima
Ako želite zbrojiti brojeve u rasponu ćelija (stupac ili redak) koji odgovaraju ćelijama koje imaju određeni tekst, možete jednostavno uključiti taj tekst ili ćeliju koja sadrži tekst u argument kriterija vaše formule SUMIF. Imajte na umu da tekstualni niz uvijek treba biti stavljen u dvostruke navodnike (” “).
Na primjer, ako želite ukupni iznos prodaje u regiji 'Zapad', možete koristiti formulu u nastavku:
=SUMIF(C2:C13,"Zapad",B2:B13)U ovoj formuli, funkcija SUMIF traži vrijednost "Zapad" u rasponu ćelija C2:C13 i zbraja odgovarajuću vrijednost prodaje u stupcu B. Zatim prikazuje rezultat u ćeliji E3.
Također se možete pozvati na ćeliju koja sadrži tekst umjesto da koristite tekst u argumentu kriterija:
=SUMIF(C2:C12,E2,B2:B12)
Sada, uzmimo ukupan prihod svih regija osim 'Zapada'. Da bismo to učinili, koristit ćemo operator not equal to () u formuli:
=SUMIF(C2:C12,""&E2,B2:B12)
SUMIF sa zamjenskim karticama
U gornjoj metodi, funkcija SUMIF s kriterijima teksta provjerava raspon u odnosu na točno specificirani tekst. Zatim zbraja parrelne brojeve u točan tekst i zanemaruje sve ostale brojeve uključujući djelomično podudarni tekstualni niz. Da biste zbrojili brojeve s djelomičnim podudarnim tekstualnim nizovima, morate prilagoditi jedan od sljedećih zamjenskih znakova u svojim kriterijima:
?(upitnik) se koristi za podudaranje s bilo kojim pojedinačnim znakom, bilo gdje u tekstualnom nizu.*(zvjezdica) se koristi za pronalaženje podudarnih riječi zajedno s bilo kojim nizom znakova.~(tilda) se koristi za podudaranje tekstova s upitnikom (?) ili znakom zvjezdice (*).
Ovaj primjer proračunske tablice za proizvode i njihove količine ćemo zbrojiti brojeve sa zamjenskim znakovima:
Zvjezdica (*) Zamjenski znak
Na primjer, ako želite zbrojiti količine svih Apple proizvoda, koristite ovu formulu:
=SUMIF(A2:A14,"Jabuka*",B2:B14)Ova SUMIF formula pronalazi sve proizvode s riječju "Jabuka" na početku i bilo kojim brojem znakova nakon nje (označeno s "*"). Nakon što se pronađe podudaranje, sažima se Količina brojevi koji odgovaraju odgovarajućim tekstualnim nizovima.
Također je moguće koristiti više zamjenskih znakova u kriterijima. Također možete unijeti zamjenske znakove s referencama na ćelije umjesto izravnog teksta.
Da biste to učinili, zamjenski znakovi moraju biti stavljeni u dvostruke navodnike (“ “) i povezani s referencama ćelije:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Ova formula zbraja količine svih proizvoda koji u sebi imaju riječ 'Redmi', bez obzira na to gdje se riječ nalazi u nizu.
Upitnik (?) Zamjenski znak
Možete koristiti zamjenski znak upitnika (?) za podudaranje tekstualnih nizova s bilo kojim pojedinačnim znakovima.
Na primjer, ako želite pronaći količine svih varijanti Xiaomi Redmi 9, možete koristiti ovu formulu:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Gornja formula traži tekstualne nizove s riječju "Xiaomi Redmi 9" nakon koje slijede bilo koji pojedinačni znak i zbraja odgovarajuće Količina brojevima.
Tilda (~) Zamjenski znak
Ako želite uskladiti stvarni upitnik (?) ili znak zvjezdice (*), umetnite znak tilde (~) ispred zamjenskog znaka u dijelu formule za uvjet.
Da biste dodali količine u stupac B s odgovarajućim nizom koji na kraju imaju znak zvjezdice, unesite formulu ispod:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Da biste dodali količine u stupac B koje imaju upitnik (?) u stupcu A u istom retku, pokušajte sljedeću formulu:
=SUMIF(A2:A14,"~?",B2:B14)
Funkcija SUMIF s kriterijima datuma
Funkcija SUMIF vam također može pomoći da uvjetno zbrojite vrijednosti na temelju kriterija datuma – na primjer, brojevi koji odgovaraju određenom datumu, ili prije datuma, ili nakon datuma. Također možete koristiti bilo koji od operatora usporedbe s vrijednošću datuma za kreiranje kriterija datuma za zbrajanje brojeva.
Datum se mora unijeti u format datuma koji podržava Google tablice, ili kao referenca ćelije koja sadrži datum, ili pomoću funkcije datuma kao što je DATE() ili DANAS().
Koristit ćemo ovaj primjer proračunske tablice da vam pokažemo kako funkcionira funkcija SUMIF s kriterijima datuma:
Pretpostavimo da želite zbrojiti iznose prodaje koji su se dogodili na dan ili prije (<=) 29. studenog 2019. u gornjem skupu podataka, možete dodati te prodajne brojeve pomoću funkcije SUMIF na jedan od ovih načina:
=SUMIF(C2:C13,"<=29. studenog 2019.",B2:B13)Gornja formula provjerava svaku ćeliju od C2 do C13 i odgovara samo za one ćelije koje sadrže datume 29. studenog 2019. ili prije (29.11.2019.). Zatim zbraja iznos prodaje koji odgovara tim odgovarajućim ćelijama iz raspona ćelija B2:B13 i prikazuje rezultat u ćelijama E3.
Datum se može unijeti u formulu u bilo kojem formatu koji prepoznaju Google tablice, kao što je "29. studenog 2019.", "29. studenog 2019." ili "29.11.2019.", itd. Zapamtite vrijednost datuma i operator mora uvijek biti stavljen u dvostruke navodnike.
Također možete koristiti funkciju DATE() u kriterijima umjesto izravne vrijednosti datuma:
=SUMIF(C2:C13,"<="&DATE(2019,11,29),B2:B13)
Ili možete koristiti referencu ćelije umjesto datuma u kriterijskom dijelu formule:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Ako želite zbrojiti iznose prodaje na temelju današnjeg datuma, možete koristiti funkciju DANAS() u argumentu kriterija.
Na primjer, da biste zbrojili sve iznose prodaje za današnji datum, koristite ovu formulu:
=SUMIF(C2:C13,DANAS(),B2:B13)
Funkcija SUMIF s praznim ili nepraznim ćelijama
Ponekad ćete možda morati zbrojiti brojeve u rasponu ćelija s praznim ili nepraznim ćelijama u istom retku. U takvim slučajevima možete koristiti funkciju SUMIF za zbrajanje vrijednosti na temelju kriterija gdje su ćelije prazne ili ne.
Zbroj ako je prazno
U Google tablicama postoje dva kriterija za pronalaženje praznih ćelija: “” ili “=”.
Na primjer, ako želite zbrojiti sav iznos prodaje koji sadrži nizove nulte duljine (vizualno izgleda prazno) u stupcu C, upotrijebite dvostruke navodnike bez razmaka između u formuli:
=SUMIF(C2:C13,"",B2:B13)
Da biste zbrojili sav iznos prodaje u stupcu B s potpunim praznim ćelijama u stupcu C, uključite "=" kao kriterij:
=SUMIF(C2:C13,"=",B2:B13)
Zbroj ako nije prazno:
Ako želite zbrojiti ćelije koje sadrže bilo koju vrijednost (ne prazne), možete koristiti "" kao kriterij u formuli:
Na primjer, da biste dobili ukupan iznos prodaje s bilo kojim datumom, koristite ovu formulu:
=SUMIF(C2:C13,"",B2:B13)
ZBIR na temelju više kriterija s logikom ILI
Kao što smo do sada vidjeli, funkcija SUMIF dizajnirana je za zbrajanje brojeva na temelju samo jednog kriterija, ali je moguće zbrajati vrijednosti na temelju više kriterija s funkcijom SUMIF u Google tablicama. To se može učiniti spajanjem više od jedne funkcije SUMIF u jednu formulu s logikom ILI.
Na primjer, ako želite zbrojiti iznos prodaje u regiji "Zapad" ili "Jug" (logika ILI) u navedenom rasponu (B2:B13), koristite ovu formulu:
=SUMIF(C2:C13,"Zapad",B2:B13)+SUMIF(C2:C13,"Jug",B2:B13)Ova formula zbraja ćelije kada je barem jedan od uvjeta TRUE. Stoga je poznat kao 'ILI logika'. Također će zbrojiti vrijednosti kada su ispunjeni svi uvjeti.
Prvi dio formule provjerava raspon C2:C13 za tekst 'Zapad' i zbraja vrijednosti u rasponu B2:B13 kada se ispuni podudaranje. Dio sekundi provjerava tekstualnu vrijednost "Jug" u istom rasponu C2:C13, a zatim zbraja vrijednosti s odgovarajućim tekstom u istom rasponu zbroja B2:B13. Zatim se oba zbroja zbrajaju i prikazuju u ćeliji E3.
U slučajevima kada je zadovoljen samo jedan kriterij, vraća samo tu vrijednost zbroja.
Također možete koristiti više kriterija umjesto samo jednog ili dva. A ako koristite više kriterija, bolje je koristiti referencu ćelije kao kriterij umjesto da pišete izravnu vrijednost u formuli.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
SUMIF s OR logikom dodaje vrijednosti kada je zadovoljen barem jedan od navedenih kriterija, ali ako želite zbrojiti vrijednosti samo kada su ispunjeni svi navedeni uvjeti, morate koristiti njegovu novu srodnu funkciju SUMIFS().
SUMIFS funkcija u Google tablicama (više kriterija)
Kada koristite funkciju SUMIF za zbrajanje vrijednosti na temelju više kriterija, formula može postati preduga i komplicirana, a vi ste skloni pogriješiti. Osim toga, SUMIF će vam omogućiti da zbrojite vrijednosti samo u jednom rasponu i kada je bilo koji od uvjeta TRUE. Tu dolazi funkcija SUMIFS.
Funkcija SUMIFS pomaže vam da zbrojite vrijednosti na temelju više podudarnih kriterija u jednom ili više raspona. I radi na logici I, što znači da može samo zbrajati vrijednosti samo kada su ispunjeni svi zadani uvjeti. Čak i ako je jedan uvjet netačan, kao rezultat će vratiti '0'.
Sintaksa i argumenti funkcije SUMIFS
Sintaksa funkcije SUMIFS je sljedeća:
=SUMIFS(zbroj_raspon, kriterij_raspon1, kriterij1, [raspon_kriterija2, ...], [kriterijum2, ...])Gdje,
- raspon_zbira – Raspon ćelija koje sadrže vrijednosti koje želite zbrojiti kada su ispunjeni svi uvjeti.
- raspon_kriterija 1 – To je raspon ćelija u kojem provjeravate kriterije1.
- kriterij 1 – To je uvjet koji trebate provjeriti u odnosu na kriterij_raspon1.
- krraspon_iteria2, kriterij2, …– Dodatni rasponi i kriteriji za ocjenjivanje. A formuli možete dodati više raspona i uvjeta.
Koristit ćemo skup podataka na sljedećoj snimci zaslona da pokažemo kako funkcija SUMIFS radi s različitim kriterijima.
SUMIFS s tekstualnim uvjetima
Možete zbrojiti vrijednosti na temelju dva različita tekstualna kriterija u različitim rasponima. Na primjer, recimo da želite saznati ukupan iznos prodaje isporučenog artikla Šator. Za to koristite ovu formulu:
=SUMIFS(D2:D13,A2:A13,"Šator",C2:C13,"Isporučeno")U ovoj formuli imamo dva kriterija: “Šator” i “Isporučeno”. Funkcija SUMIFS provjerava stavku 'Šator' (kriteriji1) u rasponu A2:A13 (raspon_kriterija1) i provjerava status 'Isporučeno' (kriteriji2) u rasponu C2:C13 (raspon_kriterija2). Kada su oba uvjeta ispunjena, tada se zbraja odgovarajuća vrijednost u rasponu ćelija D2:D13 (raspon_zbroja).
SUMIFS s brojevnim kriterijima i logičkim operatorima
Možete koristiti uvjetne operatore za stvaranje uvjeta s brojevima za funkciju SUMIFS.
Da biste pronašli ukupnu prodaju više od 5 količina bilo kojeg artikla u državi Kalifornija (CA), koristite ovu formulu:
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")Ova formula ima dva uvjeta: “>5” i “CA”.
Ova formula provjerava količine (Qty) veće od 5 u rasponu D2:D13 i provjerava stanje 'CA' u rasponu B2:B13. A kada su ispunjena oba uvjeta (što znači da su u istom redu), zbraja se iznos u E2:E13.
SUMIFS s datumskim kriterijima
Funkcija SUMIFS također vam omogućuje provjeru više uvjeta u istom rasponu, kao i različitim rasponima.
Pretpostavimo da želite provjeriti ukupan iznos prodaje isporučenih artikala nakon 31.5.2021. i prije datuma 6.10.2021., a zatim koristite ovu formulu:
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)Gornja formula ima tri uvjeta: 31/5/2021, 10/5/2021 i Isporučeno. Umjesto korištenja izravnih datumskih i tekstualnih vrijednosti, pozvali smo se na ćelije koje sadrže te kriterije.
Formula provjerava datume nakon 31. 5. 2021. (G1) i datume prije 6. 10. 2021. (G2) u istom rasponu D2:D13 i provjerava status "Isporučeno" između ta dva datuma. Zatim zbroji povezani iznos u rasponu E2:E13.
SUMIFS s praznim i nepraznim ćelijama
Ponekad ćete možda htjeti pronaći zbroj vrijednosti kada je odgovarajuća ćelija prazna ili ne. Da biste to učinili, možete koristiti jedan od tri kriterija o kojima smo prije raspravljali: “=”, “” i “”.
Na primjer, ako želite samo zbrojiti količinu stavki 'Šator' za koje datum isporuke još nije potvrđen (prazne ćelije), možete koristiti kriterije "=":
=SUMIFS(D2:D13,A2:A13,"Šator",C2:C13,"=")Formula traži stavku "Šator" (kriteriji1) u stupcu A s odgovarajućim praznim ćelijama (kriteriji2) u stupcu C, a zatim zbraja odgovarajući iznos u stupcu D. "=" predstavlja potpuno praznu ćeliju.
Da biste pronašli zbroj stavki 'Šator' za koje je potvrđen datum isporuke (ne prazne ćelije), koristite "" kao kriterij:
=SUMIFS(D2:D13,A2:A13,"Šator",C2:C13,"")Upravo smo zamijenili "=" za "" u ovoj formuli. Pronalazi zbroj stavki Šator s nepraznim ćelijama u stupcu C.
SUMIFS s OR logikom
Budući da funkcija SUMIFS radi na logici I, zbraja samo kada su ispunjeni svi uvjeti. Ali što ako želite zbrojiti vrijednost na temelju više kriterija kada je ispunjen bilo koji od kriterija. Trik je u korištenju više funkcija SUMIFS.
Na primjer, ako želite zbrojiti iznos prodaje za "Stalak za bicikle" ILI "Ruksak" kada je njihov status "Naručeno", isprobajte ovu formulu:
=SUMIFS(D2:D13,A2:A13,"Stalac za bicikl",C2:C13,"Naručeno") +SUMIFS(D2:D13,A2:A13,"Ruksak",C2:C13,"Naručeno")Prva funkcija SUMIFS provjerava dva kriterija “Stalac za bicikle” i “Naručeno” i zbraja vrijednosti iznosa u stupcu D. Zatim, drugi SUMIFS provjerava dva kriterija “Ruksak” i “Naručeno” i zbraja vrijednosti iznosa u stupcu D. , oba zbroja se zbrajaju i prikazuju na F3. Jednostavnim riječima, ova formula zbraja kada se naručuje ili 'Bike stalak' ili 'Ruksak'.
To je sve što trebate znati o funkcijama SUMIF i SUMIFS u Google tablicama.