Excel SUMIFS: Sum Sum Values ​​Zvimwe Zvinosangana Nemazano Akawanda

I-SUMIFS basa inowedzera kubatsira kweSUMIF basa nekukubvumira kuti utaure kubva pa2 kusvika kune 127 maitiro kunze kwekuti imwe chete seSUMIF.

Kazhinji, SUMIFS inoshanda nemitsetse ye data inonzi marekhodi. Mune zvinyorwa , yese yedhesi iri mumusero imwe neimwe kana munda mumutsara yakabatana - yakadai sezita rekambani, kero uye nhamba yefoni.

SUMIFS inotarisa mitsara yakananga mumatambudziko maviri kana kupfuura mune rekodhi uye kana ichinge ichitsvaga mutsara wega imwe neimwe inotsanangurwa ndiyo dhesi yeiyo rekinyorwa yakapfupiswa.

01 yegumi

Maitiro ayo SUMIFS Function Works

MuchiSUMIF nhanho nedanho redzidzisi takabatanidza tsanangudzo imwechete yevatengesi vekutengesa vakange vatengesa zvinopfuura 250 mirairo mugore.

Muchidzidzo ichi, tichaisa zvinhu zviviri tichishandisa SUMIFS - iyo yekutengesa vashandi muEast East yekutsvaga avo vaive nevasingasviki 275 kutengeswa mugore rapfuura.

Kugadzirisa zvinopfuura zviviri zvinogona kuitwa nekutsanangura mamwe Criteria_range uye mazano ekuratidza SUMIFS.

Kutevera matanho ari mumusoro wekudzidziswa pasi pano anokufambisa kuburikidza nekusika uye kushandisa SUMIFS basa rinoonekwa mumufananidzo uri pamusoro.

Zvidzidzo Misoro

• Kupinda Tutorial Data
• The SUMIFS Function's Syntax
• Kutanga SUMIFS Function
• The Sum_range Argument
• The Criteria_range1 Kurwisana
• ICriteria1 Kupokana
• The Criteria_range2 Kurwisana
• The Criteria2 Argument

02 yegumi

Kupinda Tutorial Data

Nhanho yekutanga yekushandisa SUMIFS basa iri muExcel ndiko kupinda data.

Isa data mumasero D1 kusvika F11 eExpcel sheetsheet sezvinoonekwa mumufananidzo uri pamusoro.

I-SUMIFS inoshanda uye zvitsva zvinotsvaga (mirairo inopfuura 275 nevatengi vekutengesa kubva kuEast East sales) ichawedzerwa kumutsara 12 pasi pe data.

Dzidzo yemirayiridzo haisanganisi maitiro ekufambisa pepaji.

Izvi hazvizovhiringidzi nekupedzisa tutori. Tsamba yako yebasa ichaita yakasiyana nemuenzaniso wakaratidzwa, asi SUMIFS basa ichakupa mhinduro imwe chete.

03 wegumi

The SUMIFS Function's Syntax

MuExpcel, syntax yebasa inoreva kurongeka kwebasa uye inosanganisira zita rebasa, mabheketi, uye nharo .

Mutsara weSUMIFS basa nderokuti:

= SUMIFS (Sum_ange, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

Cherechedza: Kusvika 127 Criteria_range / Criteria pairs ingarondedzerwa mubasa racho.

The SUMIFS Function's Arguments

Nharo dzebasa dzinoudza basa kuti chii chiri kuongororwa uye kuti ndezvipi zvinyorwa zvekuverengera kana izvozvi zvikaitika.

Zvose zvinopesana mubasa iri zvinodiwa.

Sum_range - data iri mumatare emasero aya inopfupikiswa apo mutambo unowanikwa pakati peCriteria yose yakatemwa uye mazano avo anowirirana neCriteria_range .

Criteria_range - boka remasero basa nderokutsvaga mutsara kune imwecheteyo Criteria argument.

Zviratidzo - kukosha uku kunoenzaniswa nedata iri muCriteria_range yakaenderana. Dhiyabhorosi chaiyo kana sero rinoshandiswa kune data rinogona kupinda mukati menyaya iyi.

04 yegumi

Kutanga SUMIFS Function

Kunyange zvazvo zvichibvira kungonyorera SUMIFS basa iri muchitokisi mune pepa rekushanda , vanhu vazhinji vanoona zviri nyore kushandisa bhokisi rebasa rebasa kuti vapinde basa.

Tutorial Steps

1. Dzvanya pachitokisi F12 kuti uite sero rinoshanda . Apa ndipo patichapinda muSUMIFS basa.
2. Dzvanya paMafomu eTom.
3. Dzvanya paMat & Trig icon paibhoni kuti uzarure basa rezasi pasi.
4. Dzvanya paSUMIFS mune urongwa kuti uunze SUMIFS's dialog box.

Dhenda yatinopinda mumiganhu isina chinhu mubhokisi rebhokisi ichaita nharo dzeSUMIFS basa.

Izvozvi nharo dzinoudza basa iro zvatiri kuedza uye kuti ndezvipi zvakasiyana zve data kuti zvikwanise kana izvozvi zvikaitika.

05 of 10

Kupinda muSun_range Argument

Iyo Sum_range nharo ine zvinyorwa zveseti kune data yatinoda kuwedzera.

Apo basa racho rinowana mutambo pakati pezvose zvinotsanangurwa Criteria uye Criteria_range nharo dzerekodha iyo Sum_range field iyo rekodhi inowanikwa muhuwandu.

Muchidzidzo ichi, data ye Sum_range argument iri mu Total Sales column.

Tutorial Steps

1. Dzvanya pane Sum_range line mubhokisi rebhokisi .
2. Simbisai masero F3 kusvika kune F9 mubasa rekuwedzera kuti uwedzere izvi zvinyorwa zvinyorwa kumutsara weSum_range .

06 of 10

Kupinda Criteria_range1 Kurwisana

Muchidzidzo ichi tiri kuedza kuenzanisa zviyero zviviri munhoroondo ye data imwe neimwe:

1. Sales sales agents kubva kuEast sales sales.
2. Vatengesi vekutengesa avo vakaita zviduku zvinopfuura 275 kutengesa gore rino.

Iko Criteria_range1 nharo inoratidza huwandu hwemasero aya SUMIFS ndeyekutsvaga paanenge achiedza kufanana nekutanga yekutanga-East East sales.

Tutorial Steps

1. Mubhokisi rebhokisi , tora paCriteria_range1 mutsara.
2. Simbisai masero D3 kusvika kuD9 mubasa rekushandisa kuti uise izvi zvinyorwa zvinyorwa sezvenzira yekutsvaga nemabasa .

07 pa10

Kupinda Criteria1 Kurwisana

Muchidzidzo ichi, yekutanga mitemo yatinotarisa kuti tifanane ndeyo kana dhesi iri muhara D3: D9 yakaenzana neEast .

Kunyange zvazvo data chaiyo - yakadai sezwi East - inogona kupinda mubhokisi rebhokisi nekuda kwekukakavadzana iyo kazhinji yakanakisa kuwedzera deta kuseri mune pepa rekushandura uye wozoisa iyo seferensi mubhokisi rebhokisi.

Tutorial Steps

1. Dzvanya paCriteria1 mutsara mubhokisi rebhokisi .
2. Dzvanya pane sero D12 kuti upinde iyo seferensi inotarisa. Basa racho richatsvaga mararamiro akasarudzwa mudanho rekare kune dhese inofanidza izvi.
3. Izwi rekutsvaga (Kumabvazuva) richawedzerwa kuseri D12 mumutsara wokupedzisira wekudzidzisa.

Nzira dzeCheferensi dzeKero Inowedzera SUMIFS Kusiyana-siyana

Kana sero rinoshandiswa, rakadai saD12, rakapinda seCriteria Argument, SUMIFS basa rinotarisa mitsva kune chero dhesi yakange yakanyorwa mukati memutsara iwoyo mubasa.

Saka mushure mokuwana kuwana mutengo wekutengesa kweEast region inenge iri nyore kuwana imwecheteyo yeimwe nzvimbo yekutengesa chete nekushandura East kusvika kuchamhembe kana kumavirazuva muchikamu D12. Basa racho richavandudza uye rinoratidza chibereko chitsva.

08 yegumi

Kupinda Criteria_range2 Kurwisana

Sezvambotaurwa kare, mune iyi tutorial tiri kuedza kuenzanisa zviyero zviviri munhoroondo imwe neimwe ye data:

1. Sales sales agents kubva kuEast sales sales.
2. Vatengesi vekutengesa avo vakaita zviduku zvinopfuura 275 kutengesa gore rino.

Iko Criteria_range2 nharo inoratidza huwandu hwemasero aya SUMIFS ndeyekutsvaga paanenge achiedza kufanana neyechipiri mitemo - vatengesi vekutengesa avo vakatengesa vashoma kupfuura mirairo 275 gore rino.

Tutorial Steps

1. Mubhokisi rebhokisi , tora paCriteria_range2 mutsara.
2. Kukurira masero eE3 kusvika E9 mubasa rekushandisa kuti uise izvi zvinyorwa zvinyorwa sechikwata chechipiri kuti chiongorwe nebasa racho .

09 yegumi

Kupinda Criteria2 Kurwisana

Muchidzidzo ichi, chechipiri nzira yatinotarisa kuti tifanane ndeyo kana dhesi iri mukati meE3: E9 iri pasi pe 275 kutengesa mirairo.

Sezvo neCriteria1 nharo , tichapinda sero rinoreva nzvimbo yeCriteria2 mubhokisi rebhokisi panzvimbo ye data pachayo.

Tutorial Steps

1. Dzvanya paCriteria2 mutsara mubhokisi rebhokisi .
2. Dzvanya pane cell E12 kuti uise iyo cell reference. Basa racho richatsvaga mararamiro akasarudzwa mudanho rekare kune dhese inofanidza izvi.
3. Dzvanya OK kuti upedze SUMIFS basa uye uvhare bhokisi rebhokisi.
4. Mhinduro ye zero (0) ichaonekwa muchitokisi F12 - sero patakapinda mubasa - nokuti hatina kuwedzera tsanangudzo kuCriteria1 uye Criteria2 minda (C12 naD12). Kusvikira taita, hapana chinhu chebasa rekuwedzera uye saka iyo yose inogara pa zero.
5. Mitsva yekutsvaga ichawedzerwa mu danho rinotevera rekudzidzisa.

10 pa10

Kuwedzera Mitsva yekutsvaga uye Kuzadzisa Tutori

Nhanho yekupedzisira mune zvidzidzo ndeyekuwedzera deta kumasero ari mubhuku rebasa rinotsanangurwa seine nharo dzeCriteria .

Tutorial Steps

Kuti ubatsirwe nemuenzaniso uyu ona mufananidzo uri pamusoro.

1. Muchitokisi D12 rudzi rweEast uye taurai Enter Enter pane keyboard.
2. Muchikamu cheE12 mutauro <275 uye pinda pikicha yoKuisa pane keyboard (i "<" ndiyo chiratidzo chepasi kudarika mu Excel).
3. Mhinduro ye $ 119,719.00 inofanira kuonekwa muchitokisi F12.
4. Iviri chete inonyora iyo iri mumiganhu ye3 ne4 inofanidza zvose zvinotarisirwa uye, naizvozvo, chete iyo yekutengesa yezvinyorwa zviviri iyo inopfupiswa nemabasa.
5. Nhamba ye $ 49,017 uye $ 70,702 ye $ 119,719.
6. Paunenge uchitsvaga pane cell F12, basa rakakwana
   = SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) inowanikwa mubhadharo yepamusoro pane pepa pepaji .