Haddii aad si joogto ah u isticmaasho Google Sheets oo aad waligaa u baahan tahay inaad qiimeyso wadar ahaan iyadoo lagu salaynayo xaalad unugyo gaar ah, markaa waxaad u baahan doontaa inaad ogaato sida loo isticmaalo shaqada SUMIF ee xaashiyaha Google^{(Google Sheets)} .

Awoodda lagu soo koobayo xogta shaqadan kuma koobna oo keliya laba qiime. Waxaad soo koobi kartaa tiro dhan. Iyo xaalada aad siiso shaqada si ay u soo koobto ama aysan ku xirnaan karin unugyo badan oo xaashidaada ku jira.

Sida Shaqada SUMIF^{(SUMIF Function Works)} ugu Shaqeyso Xaashiyaha Google

SUMIF waa shaqo fudud oo xaashida faafinta ah^{(simple spreadsheet function)} , laakiin waa dabacsanaan kugu filan si aad ugu samayso xisaab hal abuur leh.

Waxaad u baahan tahay inaad u habayso shaqada sida soo socota:

SUMIF(qiyaas, shaybaadh, [guud_range])^{(SUMIF(range, criterion, [sum_range]))}

Halbeegyada shaqadani waa sida soo socota:

• Range : Kala duwanaanta unugyada aad rabto inaad isticmaasho si aad u qiimeyso inaad ku darto qiyamka iyo in kale.
• Shuruudaha^(Criterion) : Xaaladda unugyada aad rabto inaad qiimeyso.
• Sum_range : Halbeeggani waa ikhtiyaari, waxaana ku jira unugyada aad rabto inaad soo koobto. Haddii aadan ku darin halbeeggaan, shaqadu waxay si fudud u soo koobeysaa tirada lafteeda.

Shaqadu waxay u muuqataa mid fudud, laakiin xaqiiqda ah inaad soo koobin karto ama is barbardhigi karto kala duwanaanta unugyo badan waxay kuu ogolaaneysaa dabacsanaan aad uga badan inta aad ogaan karto.

Tusaale SUMIF oo leh Qoraal

Haddii aad diyaar u tahay inaad ku bilowdo shaqada SUMIF , habka ugu fiican waa inaad isticmaasho laba tiir oo ku jira xaashidaada. Hal tiir ayaa noqon doona isbarbardhiggaaga, kan kalena wuxuu noqon doonaa qiyamka aad rabto inaad ku darto.

Xaashida tusaalaha sare waa kan mulkiilaha bakhaar kaas oo la socda wax iibsiga ilaa muddo. Mulkiilaha bakhaarku waxa uu rabaa in uu sameeyo tiirar dheeraad ah oo soo koobaya qiimaha wax iibsiga ee tiirka B ee qiimayaasha gaarka ah ee tiirka A.

Xaaladdan oo kale, qiyaasta isbarbardhigga waxay noqon doontaa A2: A15 .

Halbeeggu wuxuu noqon lahaa weedha raadinta shayga lagu daro. Marka, kiiskan, in la isku daro dhammaan iibsashada digger-ka boostada, cabirku wuxuu noqon lahaa qoraalka "Digger Post".

Qiyaasta wadarta waxay noqon doontaa tirada unugyada leh qiyamka la soo koobay. Halkan, tani waa B2:B15 .

Markaad riixdo geli, waxaad arki doontaa qiyamka gaarka ah ee sum_range column oo la isku daray, laakiin kaliya xogta^{(the data)} unugyada halka tiirka A uu ku habboon yahay cabbirka aad sheegtay.

Tani waa hab fudud oo loo isticmaalo shaqada SUMIF ; si loo soo saaro qiyamka tiirka labaad iyadoo lagu salaynayo shayada ku taxan midka hore.

Fiiro gaar^(Note) ah: Looma baahna inaad ku qorto cabbirka qaacidada gudaha laba-xigasho. Taa beddelkeeda, waxaad ku qori kartaa qiimahaas unug ku yaal xaashida oo geli unuggaas qaacidada.

Isticmaalka Hawl-wadeennada SUMIF oo wata Qoraal

Halka tusaalaha kore uu u muuqdo taqsi fiican, waxa kale oo aad isticmaali kartaa hawl wadeenada si aad u qeexdo qaybo ka mid ah qoraalka aad rabto in aad is waafajiso. Haddii aad wax ka beddesho shuruudaha raadinta, waxaad soo koobi kartaa qiyamka unugyada kuwaas oo laga yaabo in aanay si fiican u socon laakiin ku siinaya jawaabta aad raadinayso.

Isticmaalka tusaalaha sare, haddii aad rabto inaad ku darto iibsashada dhammaan alaabta marka laga reebo^(except) laydhka korontada, waxaad geli doontaa qaacidada <> operator.

=SUMIF(A2:A15,”<>Electric Drill”,B2:B15)

Hawlwadeenka <> wuxuu u sheegaa shaqada SUMIF inay iska indho tirto "Dhill koronto" laakiin ku dar dhammaan walxaha kale ee kala duwan ee B2: B15 .

Sida aad ka arki karto natiijada hoose, shaqada SUMIF waxay u shaqeysaa sidii la rabay.

Waxa kale oo aad isticmaali kartaa hawlwadeennada soo socda marka aad isticmaalayso shaqada SUMIF qoraal ahaan:

• ? Ka raadi ereyo leh jilaa kasta halka aad dhigatay?. Tusaale ahaan, "S?ovel" waxay soo koobaysaa shay kasta oo ka bilaabmaya "S" oo ku dhammaan doona "ovel" xaraf kasta oo u dhexeeya.
• * : Raadi ereyada ka bilaabma ama ku dhammaada wax kasta. Tusaale ahaan, "* Watch" waxay ku dari doontaa dhammaan shay kasta oo ah saacad kasta, iyada oo aan loo eegin sumadda.

Fiiro gaar^(Note) ah: Haddii aad runtii rabto in shaqada SUMIF ay raadiso jile sida "?" ama "*" ee qoraalka (oo ha u isticmaalin jilayaal gaar ah), dabadeed horudhac kuwa leh jilaha tilde. Tusaale ahaan, "~?" waxaa ku jiri doona "?" dabeecadda qoraalka raadinta.

Maskaxda ku hay in shaqada SUMIF aysan ahayn kiis xasaasi ah. Markaa, ma kala soocayso xarfaha waaweyn iyo kuwa yaryar marka aad qoraalka u isticmaalayso shuruudaha raadinta. Tani waa mid faa'iido leh sababtoo ah haddii isla ereyga lagu galo xarfo waaweyn ama la'aanteed, shaqada SUMIF waxay weli aqoonsan doontaa kuwa isku mid ah waxayna si habboon u soo koobeysaa qiyamka tiirka qiimaha.

Isticmaalka Hawl-wadeennada SUMIF oo leh Nambaro

Dabcan, shaqada SUMIF ee xaashida Google^{(Google Sheets)} kaliya faa'iido maaha in la helo qoraalka tiirarka leh qiimayaasha la xidhiidha si loo soo koobo. Waxa kale oo aad soo koobi kartaa tirada kala duwan ee tirooyinka buuxiya shuruudaha qaarkood.

Si aad u hubiso tirooyin kala duwan oo xaalad ah, waxaad isticmaali kartaa hawlwadeennada isbarbardhigga.

• > : Ka weyn
• < : In ka yar
• >= : Ka weyn ama la siman
• <= : In ka yar ama la mid ah

Tusaale ahaan, haddii aad haysato liis lambaro oo aad jeclaan lahayd inaad ku darto kuwa ka badan 3000, waxaad isticmaali doontaa amarka soo socda ee SUMIF .

=SUMIF(B2:B15, “>3000”)

Ogsoonow in sida shuruudaha qoraalka, uma baahnid inaad lambarka "3000" ku qorto qaacidada si toos ah. Waxaad lambarkan gelin kartaa unug oo waxaad isticmaali kartaa tixraaca unugga beddelka "3000" ee qaacidada.

Sida tan:

=SUMIF(B2:B15, “>”&C2)

Mid ka mid ah qoraallada ugu dambeeya ka hor inta aynaan eegin tusaale. Waxa kale oo aad ku soo koobi kartaa dhammaan qiimayaasha kala duwan oo le'eg tiro gaar ah, kaliya adigoon isticmaalin wax wadayaasha isbarbardhigga gabi ahaanba.

SUMIF Tusaalaha Nambarada

Aynu eegno sida aad ugu isticmaali karto shaqada SUMIF ee xaashida Google^{(Google Sheets)} -ka adiga oo isticmaalaya isbarbardhigga tirooyinka. 

Tusaalahan, ka soo qaad inaad tahay tamashle la socda dhammaan buurihii aad socotay.

Unugga D2, waxaad doonaysaa inaad ku darto wadarta joogga dhammaan buuraha ka badan 3000 cagood ee aad lugaysay. 

Si taas loo sameeyo, waxaad u baahan doontaa inaad isticmaasho qaacidada lagu sheegay qaybta sare.

Taabo Gelida^(Enter) ka dib markaad qorto qaacidada, waxaadna ku arki doontaa natiijada unuggaan.

Sida aad arki karto, shaqada SUMIF ee xaashida Google^{(Google Sheets)} waxay soo koobtay dhammaan joogga sare ee tiirka B ee buur kasta oo ka sarreeya 3000 cagood. Qaacidada SUMIF^(SUMIF) waxay iska indhatirtay dhammaan qiyamka sare ee hoos yimaada.

Isticmaal^(Use) hawlwadeenada shuruuda ah ee ku taxan qaybta u danbeysa si aad u samayso xisaab isku mid ah tirooyinka ka yar, ka weyn ama la mid ah, ka yar ama la mid ah, ama la siman.

Isticmaalka Hawl-wadeennada SUMIF ee leh Taariikhaha

Waxa kale oo aad isticmaali kartaa shaqada SUMIF oo leh taariikho. Mar labaad^(Again) , isla isbarbardhigga hawlwadeennada kor ku taxan ayaa adeegsada, si aadan uga welwelin inaad barato kuwa cusub.

Si kastaba ha ahaatee, si shaqadu u shaqeyso, taariikhuhu waxay u baahan yihiin in si sax ah loogu qaabeeyo Google Sheets marka hore.

Waxaad gacanta ku qori kartaa taariikhda shaqada ama waxaad ku qori kartaa unug oo aad tixraaci kartaa qaacidada. Qaabka tani waa sida soo socota:

=SUMIF(B2:B15, “>10/4/2019”, C2:C15)

Sida tani u shaqeyso:

1. SUMIF waxay hubin doontaa kala duwanaanshaha B2: B15 taariikh kasta kadib 10/4/2019.
2. Haddii ay run tahay, SUMIF waxay soo koobaysaa unug kasta oo ku jira C2:C15 isla safka uu isbarbardhiggani run yahay.
3. Wadarta natiijada waxaa lagu soo bandhigi doonaa unugga aad ku qortay qaaciddada.

Haddii aad haysato unug aan taariikhda sidan loo qaabayn, waxaad isticmaali kartaa hawsha DATE si aad taariikhda si habboon ugu habayso. Tusaale ahaan, haddii aad leedahay saddex unug (D2, D3, iyo D4) kuwaas oo haya sanadka, bisha, iyo maalinta, waxaad isticmaali kartaa caanaha soo socda.

Tusaale ahaan:

=SUMIF(B2:B15, “>”&DATE(D2, D3, D4), C2:C15)

Haddii aad haysato xaashi xisaabeed oo ay ku jiraan iibsigii ugu dambeeyay ee xaashida sare, waxaad si fudud u isticmaali kartaa shaqada MAANTA^(TODAY) si aad u soo koobto iibka maanta oo kaliya oo aad iska illowdo inta kale. 

=SUMIF(B2:B15, TODAY())

SUMIF ee xaashida Google^{(Google Sheets)} -ku waa mid sahlan laakiin waa wax la taaban karo^(Versatile)

Sida aad arki karto, qaacidada SUMIF ee xaashida Google^{(Google Sheets)} - ku wakhti dheer kuma qaadanayso in la barto. Laakiin siyaabaha kala duwan ee aad u isticmaali karto ayaa ka dhigaya mid aad u badan.

Haddii aad isticmaasho xaashiyo badan oo aad u baahan tahay inaad ku soo koobto qiyamka iyadoo lagu salaynayo shuruudaha qoraalka ama nambarada unugyada kale, waa inaad barataa naftaada shaqada SUMIF .

How to Use SUMIF in Google Sheets

If you regularly use Google Sheets and ever need to sum valueѕ together basеd on some condition in specific cells, then you’ll need to know how tо use the SUMIF function іn Google Sheets.

The ability to sum data with this function isn’t limited to just two values. You can sum an entire range. And the condition you provide the function for it to sum or not can depend on multiple cells in your spreadsheet too.

How the SUMIF Function Works in Google Sheets

SUMIF is a simple spreadsheet function, but it’s flexible enough so that you can do some creative calculations with it.

You need to frame the function as follows:

SUMIF(range, criterion, [sum_range])

The parameters for this function are as follows:

• Range: The range of cells you want to use to evaluate whether or not to add up the values.
• Criterion: The condition of the cells you want to evaluate.
• Sum_range: This parameter is optional, and includes the cells that you want to sum. If you don’t include this parameter, the function will simply sum up the range itself.

The function appears simple, but the fact that you can sum or compare ranges of multiple cells allows for far more flexibility than you may realize.

A SUMIF Example with Text

If you’re ready to get started with the SUMIF function, the best way is to use two columns in your spreadsheet. One column will be for your comparison, and the other will be the values you want to add.

The example sheet above is that of a store owner who’s tracking purchases through a period. The store owner wants to create additional columns that sum up the purchase prices in column B for specific values in column A.

In this case, the range for comparison would be A2:A15.

The criterion would be the search phrase for the item to add up. So, in this case, to add up all of the post digger purchases, the criterion would be the text “Post Digger”.

The sum range would be the range of cells with the values to be summed up. Here, this is B2:B15.

When you press enter, you’ll see the specific values from the sum_range column added up, but only with the data from the cells where column A matches the criterion you specified.

This is a simple way to use the SUMIF function; as a way to pluck values out of a second column based on items listed in the first.

Note: You don’t have to type the criterion into the formula inside double-quotes. Instead, you could type that value into a cell in the sheet and enter that cell into the formula.

Using SUMIF Operators with Text

While the example above looks for perfect matches, you can also use operators to specify parts of the text you want to match. If you modify the search criteria, you can sum up values for cells that may not match perfectly but provide you with the answer you’re looking for.

Using the example above, if you want to add up purchases of all items except the electric drill, you will enter the formula the <> operator.

=SUMIF(A2:A15,”<>Electric Drill”,B2:B15)

The <> operator tells the SUMIF function to ignore “Electric Drill” but add up all other items in the B2:B15 range.

As you can see from the result below, the SUMIF function works as it should.

You can also use the following operators when using the SUMIF function with text:

• ?: Search for words with any character where you’ve placed the ?. For example, “S?ovel” will sum any item starting with “S” and ending with “ovel” with any letter in between.
• *: Search for words that start or end with anything. For example, “*Watch” will add all items that are any kind of watch, regardless of brand.

Note: If you actually want the SUMIF function to search for a character like “?” or “*” in the text (and not use them as special characters), then preface those with the tilde character. For example, “~?” will include the “?” character in the search text.

Keep in mind that the SUMIF function is not case-sensitive. So, it doesn’t differentiate between capital or lower-case letters when you’re using text as the search criteria. This is useful because if the same word is entered with capital letters or without, the SUMIF function will still recognize those as a match and will properly sum up the values in the value column.

Using SUMIF Operators with Numbers

Of course, the SUMIF function in Google Sheets isn’t only useful for finding text in columns with associated values to sum up. You can also sum ranges of numbers that meet certain conditions.

To check a range of numbers for a condition, you can use a set of comparison operators.

• >: Greater than
• <: Less than
• >=: Greater than or equal to
• <=: Less than or equal to

For example, if you have a list of numbers and you’d like to add those over 3000, you would use the following SUMIF command.

=SUMIF(B2:B15, “>3000”)

Note that just like with text criteria, you don’t need to type the number “3000” into the formula directly. You could place this number into a cell and use that cell reference instead of “3000” in the formula.

Like this:

=SUMIF(B2:B15, “>”&C2)

One last note before we look at an example. You can also sum up all values in a range that are equal to a specific number, just by not using any comparison operators at all.

SUMIF Example with Numbers

Let’s look at how you can use the SUMIF function in Google Sheets by using a comparison operator with numbers. 

In this example, imagine you’re a hiker tracking all the mountains that you’ve been hiking.

In cell D2, you want to add up the total altitude of all of the mountains over 3000 feet that you’ve hiked. 

To do this, you’ll need to use the formula mentioned in the section above.

Press Enter after typing the formula, and you’ll see the results in this cell.

As you can see, the SUMIF function in Google Sheets summed up all altitude heights from column B for any mountain higher than 3000 feet. The SUMIF formula ignored all values under that altitude.

Use the other conditional operators listed in the last section to perform the same calculation for numbers less than, greater than or equal to, less than or equal to, or equal to.

Using SUMIF Operators with Dates

You can also use the SUMIF function with dates. Again, the same comparison operators listed above apply, so you don’t have to worry about learning new ones.

However, for the function to work, dates need to be formatted correctly in Google Sheets first.

You can manually type the date into the function or type it into a cell and reference it into the formula. The format for this is as follows:

=SUMIF(B2:B15, “>10/4/2019”, C2:C15)

How this works:

1. SUMIF will check the range B2:B15 for any dates after 10/4/2019.
2. If true, SUMIF will sum up any cells in C2:C15 in the same row where this comparison is true.
3. The resulting total will be displayed in the cell where you typed the formula.

If you have a cell where the date isn’t formatted this way, you can use the DATE function to reformat the date properly. For example, if you have three cells (D2, D3, and D4) that hold the year, month, and day, you can use the following formula.

For example:

=SUMIF(B2:B15, “>”&DATE(D2, D3, D4), C2:C15)

If you have a spreadsheet that contains the latest purchases at the top of the sheet, you can simply use the TODAY function to only sum up today’s purchases and ignore the rest. 

=SUMIF(B2:B15, TODAY())

SUMIF in Google Sheets is Simple But Versatile

As you can see, the SUMIF formula in Google Sheets doesn’t take a long time to learn. But the various ways you can use it make it so versatile.

If you use many spreadsheets where you need to sum up values based on the conditions of text or numbers from other cells, you should familiarize yourself with the SUMIF function.

Related posts

• Sida Loo Isticmaalo Liisaska Hoos U Dhaca Google Sheets

• 9 Qodob oo Qarsoon oo ku jira Khariidadaha Google waa inaad hubisaa

• Sida loo Shaandheeyo Google Sheets

• Waa maxay Google App-ka & Sida Loo Isticmaalo

• Sida Loo Geliyo Saxiixa Google Docs

• Sida loo damiyo Raadinta Badbaadada ah ee Google

• Waa maxay Qalabka Wariyaha Software-ka ee Chrome iyo sida loo joojiyo

• Google Hawshayda: Waa maxay sababta aad u daryeesho

• Google Search Bar Widget ma maqan yahay? Sida loo Soo Celiyo on Android

• Sida loo Kaydiyo Taariikhda Google Chrome

• Sida Loo Sameeyo Kaarka Ganacsiga Google Drive

• Sida loola socdo isbeddelada Google Docs

• Sida loogu soo diro iimaylo khaas ah Gmail

• 3 siyood oo loo sameeyo xuduudaha Google Docs

• Sida loo Hagaajiyo Khaladaadka Shahaadada Amniga SSL ee Chrome

• Sida Loo Sameeyo Garaafka Khadka Google-ka

• Waa maxay sababta Chrome uu u leeyahay habab badan oo socda?

• Sida loo tirtiro Taariikhda YouTube iyo Hawsha Goobid

• Sida Looga Sheegto Meherad Google-ka

• Waa maxay Ogeysiiska Muujinta Google ee Ilaalinta COVID?