Овај водич пружа детаљну демонстрацију како да користите функције СУМИФ и СУМИФС у Гоогле табелама са формулама и примерима.
СУМИФ је једна од математичких функција у Гоогле табелама, која се користи за условно сабирање ћелија. У основи, функција СУМИФ тражи одређени услов у опсегу ћелија и затим сабира вредности које испуњавају дати услов.
На пример, имате листу трошкова у Гоогле табелама и желите само да сумирате трошкове који су изнад одређене максималне вредности. Или имате листу артикала поруџбине и њихове одговарајуће количине, а желите да знате само укупан износ поруџбине одређене ставке. Ту је згодна функција СУМИФ.
СУМИФ се може користити за сумирање вредности на основу услова броја, услова текста, услова датума, џокер знакова, као и на основу празних и непразних ћелија. Гоогле табеле имају две функције за сумирање вредности на основу критеријума: СУМИФ и СУМИФС. Функција СУМИФ сабира бројеве на основу једног услова док СУМИФС сабира бројеве на основу више услова.
У овом водичу ћемо објаснити како да користите функције СУМИФ и СУМИФС у Гоогле табелама за сабирање бројева који испуњавају одређене услове.
Функција СУМИФ у Гоогле табелама – синтакса и аргументи
Функција СУМИФ је само комбинација функције СУМ и ИФ. Функција ИФ скенира низ ћелија за дати услов, а затим функција СУМ сабира бројеве који одговарају ћелијама које испуњавају услов.
Синтакса функције СУМИФ:
Синтакса функције СУМИФ у Гоогле табелама је следећа:
=СУМИФ(опсег, критеријуми, [опсег_збира])Аргументи:
домет - Опсег ћелија у којима тражимо ћелије које испуњавају критеријуме.
критеријума – Критеријуми који одређују које ћелије треба додати. Можете засновати критеријум на основу броја, текстуалног низа, датума, референце ћелије, израза, логичког оператора, џокер знака као и других функција.
опсег_збира – Овај аргумент је необавезан. То је опсег података са вредностима за сумирање ако се одговарајући унос опсега поклапа са условом. Ако не укључите овај аргумент, уместо тога се сабира „опсег“.
Сада, да видимо како да користимо функцију СУМИФ за сумирање вредности са различитим критеријумима.
Функција СУМИФ са критеријумима броја
Можете да збројите бројеве који испуњавају одређене критеријуме у распону ћелија, користећи један од следећих оператора поређења да бисте направили критеријум.
- веће од (>)
- мање од (<)
- веће или једнако (>=)
- мање или једнако (<=)
- једнако (=)
- неједнако са ()
Претпоставимо да имате следећу табелу и да сте заинтересовани за укупну продају од 1000 или више.
Ево како можете да унесете функцију СУМИФ:
Прво изаберите ћелију у којој желите да се појави резултат збира (Д3). Да бисте сумирали бројеве у Б2:Б12 који су већи или једнаки 1000, откуцајте ову формулу и притисните „Ентер“:
=СУМИФ(Б2:Б12,">=1000",Б2:Б12)У овом примеру формуле, аргументи опсег и опсег_збира (Б2:Б12) су исти, јер се бројеви продаје и критеријуми примењују на исти опсег. И ми смо унели број пре оператора поређења и ставили га под наводнике јер критеријуми увек треба да буду стављени у двоструке наводнике осим за референцу на ћелију.
Формула је тражила бројеве који су већи или једнаки 1000, а затим сабрала све подударне вредности и показала резултат у ћелији Д3.
Пошто су аргументи опсег и сума_ранге исти, можете постићи исти резултат без аргумената сум_ранге у формули, овако:
=СУМИФ(Б2:Б12,">=1000")
Или можете да наведете референцу ћелије (Д2) која садржи број уместо критеријума броја и придружите оператору поређења са том референцом ћелије у аргументу критеријума:
=СУМИФ(Б2:Б12,">="&Д2)Као што видите, оператор поређења се и даље уноси под двоструким наводницима, а оператор и референца ћелије су спојени знаком амперсанда (&). И не морате да стављате референцу ћелије у наводнике.
Белешка: Када упућујете на ћелију која садржи критеријуме, уверите се да не остављате никакав размак на почетку или на крају у вредности у ћелији. Ако ваша вредност има непотребан простор пре или после вредности у референтној ћелији, формула ће као резултат вратити „0“.
Такође можете користити друге логичке операторе на исти начин да поставите услове у аргументу критеријума. На пример, да се зброје вредности мање од 500:
=СУМИФ(Б2:Б12,"<500")
Збир ако су бројеви једнаки
Ако желите да додате бројеве који су једнаки одређеном броју, можете или да унесете само број или да унесете број са знаком једнакости у аргумент критеријума.
На пример, да бисте сумирали одговарајуће износе продаје (колона Б) за количине (колона Ц) чије су вредности једнаке 20, покушајте са било којом од ових формула:
=СУМИФ(Ц2:Ц12,"=20",Б2:Б12)
=СУМИФ(Ц2:Ц12,"20",Б2:Б12)
=СУМИФ(Ц2:Ц12,Е2,Б2:Б12)
Да бисте збројили бројеве у колони Б са количином која није једнака 20 у колони Ц, покушајте ову формулу:
=СУМИФ(Ц2:Ц12,"20",Б2:Б12)
Функција СУМИФ са текстуалним критеријумима
Ако желите да саберете бројеве у опсегу ћелија (колона или ред) који одговарају ћелијама које имају одређени текст, можете једноставно да укључите тај текст или ћелију која садржи текст у аргумент критеријума ваше формуле СУМИФ. Имајте на уму да текстуални низ увек треба да буде стављен у двоструке наводнике (” “).
На пример, ако желите укупан износ продаје у региону „Запад“, можете користити формулу у наставку:
=СУМИФ(Ц2:Ц13,"Запад",Б2:Б13)У овој формули, функција СУМИФ тражи вредност „Запад“ у опсегу ћелија Ц2:Ц13 и сабира одговарајућу вредност продаје у колони Б. Затим приказује резултат у ћелији Е3.
Такође можете да се позовете на ћелију која садржи текст уместо да користите текст у аргументу критеријума:
=СУМИФ(Ц2:Ц12,Е2,Б2:Б12)
Сада, хајде да добијемо укупан приход свих региона осим „Запада“. Да бисмо то урадили, користићемо не једнако оператору () у формули:
=СУМИФ(Ц2:Ц12,""&Е2,Б2:Б12)
СУМИФ са џокер картицама
У горњем методу, функција СУМИФ са текстуалним критеријумима проверава опсег у односу на тачно наведени текст. Затим сабира паррелне бројеве у тачан текст и игнорише све остале бројеве укључујући делимично подударни текстуални низ. Да бисте збројили бројеве са делимичним подударним текстуалним низовима, морате да прилагодите један од следећих џокер знакова у својим критеријумима:
?(знак питања) се користи за подударање са било којим појединачним знаком, било где у текстуалном низу.*(звездица) се користи за проналажење одговарајућих речи заједно са било којим низом знакова.~(тилда) се користи за подударање текстова са знаком питања (?) или знаком звездице (*).
Навешћемо овај пример табеле за производе и њихове количине да бисмо збрали бројеве помоћу џокер знакова:
Звездица (*) Џокерски знак
На пример, ако желите да збројите количине свих Аппле производа, користите ову формулу:
=СУМИФ(А2:А14,"Јабука*",Б2:Б14)Ова СУМИФ формула проналази све производе са речју „Јабука“ на почетку и било којим бројем знакова после ње (означено са „*“). Када се пронађе подударање, сумира се Количина бројеви који одговарају одговарајућим текстуалним низовима.
Такође је могуће користити више џокер знакова у критеријумима. Такође можете да унесете џокер знакове са референцама на ћелије уместо директног текста.
Да бисте то урадили, џокер знакови морају бити стављени у двоструке наводнике (“ “) и повезани са референцама ћелије:
=СУМИФ(А2:А14,"*"&Д2&"*",Б2:Б14)Ова формула сабира количине свих производа који имају реч „Редми“ у себи, без обзира на то где се реч налази у низу.
Знак питања (?) Џокер
Можете да користите џокер знак питања (?) да бисте упарили текстуалне низове са било којим појединачним карактером.
На пример, ако желите да пронађете количине свих Ксиаоми Редми 9 варијанти, можете користити ову формулу:
=СУМИФ(А2:А14,"Ксиаоми Редми 9?",Б2:Б14)Горња формула тражи текстуалне низове са речју „Ксиаоми Редми 9“ праћено било којим појединачним карактером и збраја одговарајуће Количина бројевима.
Тилда (~) Замјенски знак
Ако желите да ускладите стварни знак питања (?) или знак звездице (*), уметните знак тилде (~) испред џокер знака у делу формуле.
Да бисте додали количине у колону Б са одговарајућим низом који на крају имају знак звездице, унесите формулу испод:
=СУМИФ(А2:А14,"Самсунг Галаки В~*",Б2:Б14)
Да бисте додали количине у колону Б које имају знак питања (?) у колони А у истом реду, испробајте формулу испод:
=СУМИФ(А2:А14,"~?",Б2:Б14)
Функција СУМИФ са критеријумима датума
Функција СУМИФ вам такође може помоћи да условно збројите вредности на основу критеријума датума – на пример, бројеви који одговарају одређеном датуму, или пре датума, или после датума. Такође можете користити било који од оператора поређења са вредношћу датума да бисте креирали критеријуме датума за сабирање бројева.
Датум се мора унети у формат датума који подржава Гоогле табеле, или као референца на ћелију која садржи датум, или помоћу функције датума као што је ДАТЕ() или ДАНАС().
Користићемо овај пример табеле да вам покажемо како функционише функција СУМИФ са критеријумима датума:
Претпоставимо да желите да збројите износе продаје који су се десили на дан или пре (<=) 29. новембра 2019. у горњем скупу података, можете додати те бројеве продаје користећи СУМИФ функцију на један од ових начина:
=СУМИФ(Ц2:Ц13,"<=29. новембар 2019",Б2:Б13)Горња формула проверава сваку ћелију од Ц2 до Ц13 и подудара се само са оним ћелијама које садрже датуме 29. новембра 2019. или раније (29.11.2019.). Затим сабира износ продаје који одговара оним одговарајућим ћелијама из опсега ћелија Б2:Б13 и приказује резултат у ћелијама Е3.
Датум се може унети у формулу у било ком формату који препознају Гоогле табеле, на пример „29. новембар 2019′, „29. новембар 2019′“, или „29. 11. 2019.“ итд. Запамтите вредност датума и оператер мора увек бити стављен под двоструке наводнике.
Такође можете користити функцију ДАТЕ() у критеријумима уместо директне вредности датума:
=СУМИФ(Ц2:Ц13,"<="&ДАТЕ(2019,11,29),Б2:Б13)
Или, можете користити референцу ћелије уместо датума у критеријумском делу формуле:
=СУМИФ(Ц2:Ц13,"<="&Е2,Б2:Б13)
Ако желите да додате износе продаје на основу данашњег датума, можете користити функцију ДАНАС() у аргументу критеријума.
На пример, да бисте сумирали све износе продаје за данашњи датум, користите ову формулу:
=СУМИФ(Ц2:Ц13,ДАНАС(),Б2:Б13)
Функција СУМИФ са празним или непразним ћелијама
Понекад ћете можда морати да збројите бројеве у опсегу ћелија са празним или непразним ћелијама у истом реду. У таквим случајевима, можете користити функцију СУМИФ за сумирање вредности на основу критеријума где су ћелије празне или не.
Зброј ако је празно
Постоје два критеријума у Гоогле табелама за проналажење празних ћелија: „“ или „=“.
На пример, ако желите да збројите сав износ продаје који садржи низове нулте дужине (визуелно изгледа празно) у колони Ц, користите двоструке наводнике без размака између у формули:
=СУМИФ(Ц2:Ц13,"",Б2:Б13)
Да бисте сумирали сав износ продаје у колони Б са потпуним празним ћелијама у колони Ц, укључите „=“ као критеријум:
=СУМИФ(Ц2:Ц13,"=",Б2:Б13)
Збир ако није празно:
Ако желите да збројите ћелије које садрже било коју вредност (не празне), можете користити „“ као критеријум у формули:
На пример, да бисте добили укупан износ продаје са било којим датумом, користите ову формулу:
=СУМИФ(Ц2:Ц13,"",Б2:Б13)
СУМИФ Засновано на више критеријума са ИЛИ логиком
Као што смо до сада видели, функција СУМИФ је дизајнирана да сабира бројеве на основу само једног критеријума, али је могуће сабирати вредности на основу више критеријума помоћу функције СУМИФ у Гоогле табелама. То се може урадити спајањем више од једне функције СУМИФ у једну формулу са ИЛИ логиком.
На пример, ако желите да збројите износ продаје у региону „Запад“ или „Југ“ (логика ИЛИ) у наведеном опсегу (Б2:Б13), користите ову формулу:
=СУМИФ(Ц2:Ц13,"Запад",Б2:Б13)+СУМИФ(Ц2:Ц13,"Југ",Б2:Б13)Ова формула сабира ћелије када је бар један од услова ТРУЕ. Отуда је познато као 'ИЛИ логика'. Такође ће сабрати вредности када су испуњени сви услови.
Први део формуле проверава опсег Ц2:Ц13 за текст „Запад“ и сабира вредности у опсегу Б2:Б13 када се испуни подударање. Део секунди проверава текстуалну вредност „Југ“ у истом опсегу Ц2:Ц13, а затим сабира вредности са одговарајућим текстом у истом опсегу_збира Б2:Б13. Затим се оба збира сабирају и приказују у ћелији Е3.
У случајевима када је испуњен само један критеријум, враћа само ту вредност суме.
Такође можете користити више критеријума уместо само једног или два. А ако користите више критеријума, боље је да користите референцу ћелије као критеријум уместо да пишете директну вредност у формули.
=СУМИФ(Ц2:Ц13,Е2,Б2:Б13)+СУМИФ(Ц2:Ц13,Е3,Б2:Б13)+СУМИФ(Ц2:Ц13,Е4,Б2:Б13)
СУМИФ са ОР логиком додаје вредности када је испуњен бар један од наведених критеријума, али ако желите да збројите вредности само када су испуњени сви наведени услови, морате да користите његову нову сродну функцију СУМИФС().
СУМИФС функција у Гоогле табелама (више критеријума)
Када користите функцију СУМИФ за сабирање вредности на основу више критеријума, формула може постати предуга и компликована, а ви сте склони грешкама. Осим тога, СУМИФ ће вам омогућити да збројите вредности само у једном опсегу и када је било који од услова ТРУЕ. Ту долази функција СУМИФС.
Функција СУМИФС вам помаже да саберете вредности на основу више критеријума подударања у једном или више опсега. И ради на логици И, што значи да може само да зброји вредности само када су испуњени сви дати услови. Чак и ако је један услов нетачан, као резултат ће вратити „0“.
Синтакса и аргументи функције СУМИФС
Синтакса СУМИФС функције је следећа:
=СУМИФС(опсег_збира, критеријум_опсег1, критеријум1, [опсег_критеријума2, ...], [критеријум2, ...])Где,
- опсег_збира – Опсег ћелија који садржи вредности које желите да збројите када су испуњени сви услови.
- критеријум_опсег1 – То је опсег ћелија у којем проверавате критеријуме1.
- критеријуми1 – То је услов који треба да проверите у односу на критеријум_опсег1.
- критериа_ранге2, критеријум2, …– Додатни распони и критеријуми за процену. И можете додати више опсега и услова у формулу.
Користићемо скуп података на следећем снимку екрана да покажемо како функција СУМИФС функционише са различитим критеријумима.
СУМИФС са текстуалним условима
Можете да збројите вредности на основу два различита критеријума текста у различитим опсезима. На пример, рецимо да желите да сазнате укупан износ продаје испорученог артикла Шатор. За ово користите ову формулу:
=СУМИФС(Д2:Д13,А2:А13,"Шатор",Ц2:Ц13,"Испоручено")У овој формули имамо два критеријума: „Шатор“ и „Испоручено“. Функција СУМИФС проверава ставку „Шатор“ (критеријум1) у опсегу А2:А13 (опсег критеријума1) и проверава статус „Испоручено“ (критеријум2) у опсегу Ц2:Ц13 (опсег критеријума2). Када су оба услова испуњена, онда се сабира одговарајућа вредност у опсегу ћелија Д2:Д13 (опсег_збира).
СУМИФС са бројевним критеријумима и логичким операторима
Можете користити условне операторе да креирате услове са бројевима за функцију СУМИФС.
Да бисте пронашли укупну продају више од 5 количина било које ставке у држави Калифорнија (ЦА), користите ову формулу:
=СУМИФС(Е2:Е13,Д2:Д13,">5",Б2:Б13,"ЦА")Ова формула има два услова: „>5“ и „ЦА“.
Ова формула проверава количине (Кти) веће од 5 у опсегу Д2:Д13 и проверава стање „ЦА“ у опсегу Б2:Б13. А када су испуњена оба услова (што значи да се налазе у истом реду), то збраја износ у Е2:Е13.
СУМИФС са критеријумима датума
СУМИФС функција вам такође омогућава да проверите више услова у истом опсегу, као и различите опсеге.
Претпоставимо да желите да проверите укупан износ продаје испоручених артикала након 31.5.2021. и пре датума 6.10.2021., а затим користите ову формулу:
=СУМИФС(Е2:Е13,Д2:Д13,">"&Г1,Д2:Д13,"<"&Г2,Ц2:Ц13,Г3)Горња формула има три услова: 31/5/2021, 10/5/2021 и Испоручено. Уместо да користимо директне вредности датума и текста, позвали смо се на ћелије које садрже те критеријуме.
Формула проверава датуме после 31.5.2021 (Г1) и датуме пре 6.10.2021 (Г2) у истом опсегу Д2:Д13 и проверава статус „Достављено“ између та два датума. Затим сабира одговарајући износ у опсегу Е2:Е13.
СУМИФС са празним и непразним ћелијама
Понекад ћете можда желети да пронађете збир вредности када је одговарајућа ћелија празна или не. Да бисте то урадили, можете користити један од три критеријума о којима смо раније говорили: „=“, „“ и „“.
На пример, ако желите само да збројите количину „шаторских“ ставки за које датум испоруке још није потврђен (празне ћелије), можете користити критеријуме „=“:
=СУМИФС(Д2:Д13,А2:А13,"Шатор",Ц2:Ц13,"=")Формула тражи ставку „Шатор“ (критеријум1) у колони А са одговарајућим празним ћелијама (критеријумима2) у колони Ц, а затим сабира одговарајући износ у колони Д. „=“ представља потпуно празну ћелију.
Да бисте пронашли збирну количину ставки „Шатор“ за које је потврђен датум испоруке (не празне ћелије), користите „“ као критеријум:
=СУМИФС(Д2:Д13,А2:А13,"Шатор",Ц2:Ц13,"")Управо смо заменили „=“ за „“ у овој формули. Проналази збир ставки шатора са ћелијама које нису празне у колони Ц.
СУМИФС са ОР логиком
Пошто функција СУМИФС ради на логици И, она збраја само када су испуњени сви услови. Али шта ако желите да збројите вредност на основу више критеријума када је испуњен било који од критеријума. Трик је у употреби више функција СУМИФС.
На пример, ако желите да саберете износ продаје за „Сталак за бицикле“ ИЛИ „Руксак“ када је њихов статус „Наручено“, испробајте ову формулу:
=СУМИФС(Д2:Д13,А2:А13,"Сталац за бицикле",Ц2:Ц13,"Наручено") +СУМИФС(Д2:Д13,А2:А13,"Руксак",Ц2:Ц13,"Наручено")Прва функција СУМИФС проверава два критеријума „Сталац за бицикле“ и „Наручено“ и збраја вредности износа у колони Д. Затим, друга СУМИФС проверава два критеријума „Руксак“ и „Наручено“ и збраја вредности износа у колони Д. И онда , оба сума се сабирају и приказују на Ф3. Једноставним речима, ова формула се збраја када се наручује или „сталка за бицикле“ или „ранца“.
То је све што треба да знате о функцијама СУМИФ и СУМИФС у Гоогле табелама.