Skip to main content

Conas a lua go bhfuil cill in aice láimhe cothrom, bán nó téacs ina bhfuil Excel?

Agus Microsoft Excel á úsáid agat, b’fhéidir go mbeidh ort luachanna a shuimiú nuair is ionann an chill in aice láimhe agus critéar i raon áirithe, nó na luachanna a shuimiú ina bhfuil an chill in aice láimhe bán nó ina bhfuil téacs. Sa rang teagaisc seo, cuirfimid foirmlí ar fáil duit chun déileáil leis na fadhbanna seo.

Is ionann cill Sumif in aice láimhe agus critéar in Excel
Tá cill in aice láimhe Sumif bán in Excel
Sumif cill in aice láimhe ina bhfuil téacs in Excel

Is ionann cill Sumif in aice láimhe agus critéar in Excel

Mar a thaispeánann thíos an scáileán, ní mór duit praghsanna uile na Mairteola a shuimiú. Déan mar a leanas le do thoil.

1. Sonraigh cill chun an toradh a thaispeáint.

2. Cóipeáil agus greamaigh an fhoirmle =SUM(IF(A4:E10=A13, B4:F10, 0)) isteach sa Bar Foirmle, agus ansin brúigh Ctrl + Shift + Iontráil eochracha ag an am céanna chun an toradh a fháil.

nótaí: San fhoirmle, is é A4: E10 an raon sonraí ina bhfuil na critéir shonracha a theastaíonn uait, is é A13 na critéir ar mhaith leat na cealla a shuimiú bunaithe orthu, agus is é B4: F10 an raon ag a bhfuil na luachanna a theastaíonn uait a achoimriú. . Athraigh an raon bunaithe ar do chuid sonraí féin le do thoil.

Déan dúbailtí i gcolún agus luachanna suime a chomhcheangal go héasca i gcolún eile bunaithe ar na dúblacha in Excel

An Kutools le haghaidh Excel's Sraitheanna Comhcheangail Casta cuidíonn fóntais leat sraitheanna dúblacha a chur le chéile i gcolún agus luachanna i gcolún eile a ríomh nó a chomhcheangal bunaithe ar na dúblacha in Excel
Íoslódáil agus bain triail as anois! (trasáil saor in aisce 30 lá)

Tá cill in aice láimhe Sumif bán in Excel

Mar shampla, tá raon A2: B7 agat agus ní gá duit ach na luachanna a shuimiú ina bhfuil na cealla cóngaracha bán. Féach an pictiúr thíos. Ní mór duit a dhéanamh mar a leanas.

1. Roghnaigh cill bhán chun an toradh a thaispeáint. Cóipeáil agus greamaigh an fhoirmle = SUM (IF (ISBLANK (B2: B7), A2: A7,0)) (B2: Is é B7 an raon sonraí ina bhfuil na cealla bána, agus is é A2: A7 na sonraí a theastaíonn uait a achoimriú) isteach sa Bharra Foirmle, ansin brúigh Ctrl + Shift + Iontráil eochracha ag an am céanna chun an toradh a fháil.

Ansin is féidir leat a fheiceáil go ndéantar na luachanna go léir ina bhfuil na cealla cóngaracha bán a achoimriú agus a thaispeáint sa chill shonraithe.

Sumif cill in aice láimhe ina bhfuil téacs in Excel

Cuidíonn an modh thuas leat luachanna a shuimiú sa chás go bhfuil an chill in aice láimhe bán i raon áirithe. Sa chuid seo, foghlaimeoidh tú conas luachanna a shuimiú i gcás ina bhfuil téacsanna in Excel sna cealla cóngaracha.

Ná húsáid ach an sampla céanna leis an modh thuas a thaispeántar.

1. Roghnaigh cill bhán, cóipeáil agus greamaigh an fhoirmle = SUMIF (B2: B7, "<>" & "", A2: A7) (B2: Is é B7 an raon sonraí ina bhfuil na cealla téacs, agus is é A2: A7 na sonraí a theastaíonn uait a achoimriú) isteach sa Bharra Foirmle, ansin brúigh Ctrl + Shift + Iontráil eochracha.

Feicfidh tú na luachanna go léir ina bhfuil téacsanna sna cealla cóngaracha a achoimrítear agus a thaispeántar sa chill roghnaithe.

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (14)
No ratings yet. Be the first to rate!
Cole
Cole
This comment was minimized by the moderator on the site
Sumif Adjacent Cell Equals A Criterion In Excel formula is not working for me, it is adding everything instead of just the cells with data we want.
Reply
0
0
crystal
crystal     Cole
This comment was minimized by the moderator on the site
Hi Cole,
Which Excel version are you using? Would you mind providing a screenshot of your data?
Reply
Report
0
0
erin
erin
This comment was minimized by the moderator on the site
Hello I am trying to set up a pricing list. I have used a dropdown list for the selected products. How do I make excel input a price based on the choice of the text in the dropdown list.
Reply
0
0
crystal
crystal     erin
This comment was minimized by the moderator on the site
Hi erin,
Methods in this article can help you. Please have a try: https://www.extendoffice.com/documents/excel/2400-excel-drop-down-list-auto-populate.html
Reply
Report
0
0
wthomas
wthomas
This comment was minimized by the moderator on the site
I am trying to get a return value of "yes" if A1:A7 contains any text. What formulas can I use?
Reply
Report
0
0
crystal
crystal     wthomas
This comment was minimized by the moderator on the site
Dear Wyman Thomas,
Supposing you need to return value of "Yes" if cell A1 contains "Apple", this formula can help you =IF(ISNUMBER(SEARCH("Apple",A1)), "Yes", "No").
Reply
Report
0
0
V
V
This comment was minimized by the moderator on the site
Pretty sure

=SUMIF(B2:B7,"<>"&"",A2:B7) should be


=SUMIF(B2:B7,"<>"&"",A2:A7)
Reply
0
0
Md Islam
Md Islam     V
This comment was minimized by the moderator on the site
Few People Notice that, We take it A2:A7
Reply
0
0
crystal
crystal     V
This comment was minimized by the moderator on the site
Hi,
Thank you for your reminding.
Reply
Report
0
0
Jorge Barbi Martins
Jorge Barbi Martins
This comment was minimized by the moderator on the site
Dear all. I would like to use the SUMIF function in the following way: -Sum range is 1:1 -Criteria Range is 2:2 -Criteria is "jorge" However, I would like the function o work a little bit different. If "jorge" is found on B2, I would like A1 to be sum instead of A2. I tried =SUMIF(1:1,OFFSET(2:2,,1),"jorge") but excel does not accept such criteria range argument. How to get it working?
Reply
0
0
Mike
Mike
This comment was minimized by the moderator on the site
I am looking to create a budget spreadsheet. My goal is to plug in my daily expenses, with the category in one cell and the amount in the cell beneath. As far as my spending goes, I have no problem calculating. However, I am struggling with a formula to create a running tally of each category. Can you help?
Reply
0
0
Joshua
Joshua     Mike
This comment was minimized by the moderator on the site
Mike I am working on the same problem I created a drop down list for column D and my costs are in column C. Simply change the name (Contracts) of the formula to your category name. =SUMIF(D2:D31,""Contracts"",C2:C31)
Reply
0
0
jayson
jayson
This comment was minimized by the moderator on the site
hi, hope you could help me on this i want to create a formula where-in the codes under the subnet, be reflected on the net. see below place(net) 1 place1 2 place2 3 place3 4 place4 5 place5 where place net should have a code 1+2+3+4+5, i know how to concatenate and use "&" but i need to have a formula where i could drag it down since its not the only net in my specs, hoping for your answer? bog thanks regards, jayson
Reply
0
0
Wyman
Wyman     jayson
This comment was minimized by the moderator on the site
I would like to get the return value "yes" if the cells in A1:A7 contain data. What formula can I use?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations