Skip to main content

Conas luach sonrach a chomhaireamh thar iliomad bileoga oibre?

Ag ceapadh, tá iliomad bileoga oibre agam ina bhfuil na sonraí seo a leanas, agus anois, ba mhaith liom an líon tarlú de luach sonrach “Excel” a fháil ó bhileoga oibre tráchtais. Conas a d’fhéadfainn luachanna ar leith a chomhaireamh thar bhileog oibre iolrach?

doc-comhaireamh-trasna-il-bhileoga-1 doc-comhaireamh-trasna-il-bhileoga-2 doc-comhaireamh-trasna-il-bhileoga-3 2 doc-comhaireamh-trasna-il-bhileoga-4

Déan luach sonrach a chomhaireamh thar iliomad bileoga oibre le foirmlí

Déan luach sonrach a chomhaireamh thar iliomad bileoga oibre le Kutools for Excel

  Déan luach sonrach a chomhaireamh thar iliomad bileoga oibre le foirmlí

In Excel, tá foirmle ann chun luachanna áirithe ó iliomad bileoga oibre a chomhaireamh. Déan mar a leanas le do thoil:

1. Liostaigh na hainmneacha bileoige go léir ina bhfuil na sonraí a theastaíonn uait a chomhaireamh i gcolún amháin cosúil leis an bpictiúr seo a leanas a thaispeántar:

doc-comhaireamh-trasna-il-bhileoga-5

2. I gcill bhán, iontráil an fhoirmle seo le do thoil: =SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2)), ansin brúigh Iontráil eochair, agus gheobhaidh tú uimhir an luacha “Excel” sna bileoga oibre seo, féach an scáileán:

doc-comhaireamh-trasna-il-bhileoga-6

Nótaí:

1. San fhoirmle thuas:

  • A2: A6 an bhfuil an raon sonraí ar mhaith leat an luach sonraithe a chomhaireamh ar fud bileoga oibre;
  • C2: C4 an bhfuil liosta ainmneacha na mbileog ann a chuimsíonn na sonraí a theastaíonn uait a úsáid;
  • E2 an bhfuil na critéir a theastaíonn uait bunaithe ar.

2. Má tá iliomad bileoga oibre ann is gá a liostáil, is féidir leat an t-alt seo a léamh Conas Ainmneacha Bileog Oibre a Liostú in Excel? chun déileáil leis an tasc seo.

3. In Excel, is féidir leat feidhm COUNTIF a úsáid freisin chun an bhileog oibre a chur ceann ar cheann, déan leis an bhfoirmle seo a leanas le do thoil: =COUNTIF(Sheet1!A2:A6,D2)+COUNTIF(Sheet10!A2:A6,D2)+COUNTIF(Sheet15!A2:A6,D2), (Sheet1, Sheet10 agus Sheet15 is iad na bileoga oibre a theastaíonn uait a chomhaireamh, D2 na critéir ar a bhfuil tú bunaithe), agus ansin brúigh Iontráil eochair chun an toradh a fháil. Féach an pictiúr:

doc-comhaireamh-trasna-il-bhileoga-7

Déan luach sonrach a chomhaireamh thar iliomad bileoga oibre le Kutools for Excel

Má tá tú Kutools le haghaidh Excel, Lena Nascleanúint pána, is féidir leat an luach sonrach a liostáil agus a chomhaireamh go tapa ar fud ilbhileog oibre.

Kutools le haghaidh Excel : le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. 
Téigh go dtí Íoslódáil
Triail In Aisce 30 láCeannach
PayPal / MyCommerce

Tar éis a shuiteáil Kutools le haghaidh Excel, déan léas mar a leanas:

1. Cliceáil Kutools > Nascleanúint, féach ar an scáileán:

doc-comhaireamh-trasna-il-bhileoga-9

doc-comhaireamh-trasna-il-bhileoga-10

2. Sa an Nascleanúint pána, déan na hoibríochtaí seo a leanas le do thoil:

(1.) Cliceáil Aimsigh agus Ionadaigh cnaipe chun an Aimsigh agus Ionadaigh pána;

(2.) Clóscríobh an luach sonrach sa Aimsigh cad Bosca téacs;

(3.) Roghnaigh raon feidhme cuardaigh ón Whithin titim anuas, sa chás seo, roghnóidh mé Bileog Roghnaithe;

(4.) Ansin roghnaigh na bileoga ar mhaith leat na luachanna sonracha a chomhaireamh ón Leabhair oibre bosca liosta;

(5.) Seiceáil Meaitseáil cill iomlán más mian leat na cealla a mheaitseáil go cruinn;

(6.) Ansin cliceáil ar an Faigh Gach cnaipe chun na luachanna sonracha go léir ó iliomad bileoga oibre a liostáil, agus taispeántar líon na gcealla ag bun an phána.

Íoslódáil agus triail saor in aisce Kutools le haghaidh Excel Now!

Taispeántas: Cuir luach sonrach ar fud iliomad bileoga oibre le Kutools le haghaidh Excel

Kutools le haghaidh Excel: le níos mó ná 300 breiseán áisiúil Excel, saor in aisce le triail gan aon teorannú i 30 lá. Íoslódáil agus triail saor in aisce Anois!

Earraí gaolmhara:

Conas countif a úsáid chun an céatadán in Excel a ríomh?

Conas comhaireamh a dhéanamh le ilchritéir in Excel?

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 (15)
No ratings yet. Be the first to rate!
Fisher
Fisher
This comment was minimized by the moderator on the site
Hello, I've used the =SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2)) successfully to total criteria for sheetlists by month for 2023. I want to continue on the same workbook for 2024 and make new sheetlists by month. When i try the above formula with 2024 sheetlists, I get an REF error that i cant figure out how to resolve. Is there any way to resolve the error or do i have to start a new workbook?
Reply
0
0
Leek
Leek
This comment was minimized by the moderator on the site
Ik krijg alleen maar foutmeldingen na het kopieren-plakken van het voorbeeld en lezen van de tutorial. Ook na cellen veranderen en dergelijke.

Hoe kan ik de getallen in cellen over meerdere sheets/tabbladen bij elkaar optellen?
Reply
0
0
skyyang
skyyang     Leek
This comment was minimized by the moderator on the site
Hi, Leek,
to sum values from multiple worksheets, please apply htis formula: =SUM(Sheet1!B5,Sheet2!B5,Sheet3!B5…)

Notes:
1. In this formula, Sheet1!B5, Sheet2!B5, Sheet3!B5 are the sheet name and cell value that you wan tto sum, if there are more sheets, you just add them into the formula as you need.
2. If the source worksheet name contains a space or special character, it must be wrapped in single quotes. For example: 'New York'!B5

Please try it, hope can help you!
Reply
Report
0
0
Francisco
Francisco
This comment was minimized by the moderator on the site
Thank you. Even though is very long formula, the one that worked the easiest for me was COUNTIF + COUNTIF
Reply
0
0
Fabian
Fabian
This comment was minimized by the moderator on the site
This doesn't work when the sheet name has dashes in them. Anything I can do about it?
Reply
0
0
Bhupendra
Bhupendra
This comment was minimized by the moderator on the site
Need a answer very eagerly for this. I have multiple worksheets in our workbook, and I want to count total number for values in a column C for every sheet and that too every sheet wise not a total of every C column of all sheets.
Reply
0
0
emran
emran
This comment was minimized by the moderator on the site
1*1=1 but from 2 it should be 3 double. how i set up in excel? please give me a solution.
Reply
0
0
Mike
Mike
This comment was minimized by the moderator on the site
This was extremely helpful. the below formula is what i use to track all my employees Vacation,Sick,Personal,LOA days. I have tabs for every month of the year and use this to track how many days they have used. All i do is change the "V" to a "S" or "P". Formula works perfect! thanks for the info....

=COUNTIF(January!B16:AF16,"V")+COUNTIF(February!B16:AF16,"V")+COUNTIF(March!B16:AF16,"V")+COUNTIF(April!B16:AF16,"V")+COUNTIF(May!B16:AF16,"V")+COUNTIF(June!B16:AF16,"V")+COUNTIF(July!B16:AF16,"V")+COUNTIF(August!B16:AF16,"V")+COUNTIF(September!B16:AF16,"V")+COUNTIF(October!B16:AF16,"V")+COUNTIF(November!B16:AF16,"V")+COUNTIF(December!B16:AF16,"V")
Reply
0
0
Hari
Hari     Mike
This comment was minimized by the moderator on the site
I want to count specific word in Coloum accross the Jan to Dec, Please advice and sum it up
Reply
0
0
David
David
This comment was minimized by the moderator on the site
Hi. Using this formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2))

I get a Ref error if Column C has more than 9 rows. In other words, INDIRECT("'"&C2:C9&"'!A2:A6"),E2)), is OK but INDIRECT("'"&C2:C10&"'!A2:A6"),E2)) is not.
How do I avoid this error?
Reply
0
0
foxx
foxx
This comment was minimized by the moderator on the site
How would I write the formula above (=SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2))) if I had to use the COUNTIFS function? Right now I have to see if certain cells have met 2 to 3 criteria (across multiple worksheets). cheers for you help.
Reply
0
0
matt
matt     foxx
This comment was minimized by the moderator on the site
try =SUMPRODUCT(COUNTIFS(INDIRECT("'"&C2:C4&"'!A2:A6"),E2),(INDIRECT("'"&C2:C4&"'!A2:A6"),X2),(INDIRECT("'"&C2:C4&"'!A2:A6"),Y2)) where E2, X2 and Y2 are your criteria and INDIRECT("'"&C2:C4&"'!A2:A6" are your criteria range, where C2:C4 are your tab names and A2:A6 is your data range let me know if it works I'm actually very curious, didn't have time to run and test it myself. I just had to use one criteria so above easier formula did work wonders for me
Reply
0
0
juntjoo
juntjoo
This comment was minimized by the moderator on the site
Can you not specify your range of cells within a RANGE of sheets instead of "sheet1+sheet2+sheet3...." and specify a range to compare to like: countif(sheet1:sheet5!b:b, a:a)
Reply
0
0
matt
matt     juntjoo
This comment was minimized by the moderator on the site
tried it, didn't work. you need your tab(sheet) names in the cells for it to work. The cells are actually making up a range themselves, hence C2:C4 in the formula
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations