Skip i'r prif gynnwys

Sut i gyfrif gwerth penodol ar draws sawl taflen waith?

Gan dybio, mae gen i sawl taflen waith sy'n cynnwys y data a ganlyn, ac yn awr, rydw i eisiau cael nifer yr achosion o werth penodol “Excel” o'r taflenni gwaith traethodau ymchwil hyn. Sut allwn i gyfrif gwerthoedd penodol ar draws sawl taflen waith?

doc-count-across-multiple-sheet-1 doc-count-across-multiple-sheet-2 doc-count-across-multiple-sheet-3 2 doc-count-across-multiple-sheet-4

Cyfrif gwerth penodol ar draws nifer o daflenni gwaith gyda fformwlâu

Cyfrif gwerth penodol ar draws taflenni gwaith lluosog gyda Kutools ar gyfer Excel


  Cyfrif gwerth penodol ar draws nifer o daflenni gwaith gyda fformwlâu

Yn Excel, mae fformiwla i chi gyfrif rhai gwerthoedd o sawl taflen waith. Gwnewch fel a ganlyn:

1. Rhestrwch yr holl enwau dalennau sy'n cynnwys y data rydych chi am ei gyfrif mewn un golofn fel y screenshot canlynol a ddangosir:

doc-count-across-multiple-sheet-5

2. Mewn cell wag, nodwch y fformiwla hon: =SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!A2:A6"),E2)), yna pwyswch Rhowch allwedd, a byddwch yn cael rhif y gwerth “Excel” yn y taflenni gwaith hyn, gweler y screenshot:

doc-count-across-multiple-sheet-6

Nodiadau:

1. Yn y fformiwla uchod:

  • A2: A6 yw'r ystod ddata rydych chi am gyfrif y gwerth penodedig ar draws taflenni gwaith;
  • C2: C4 yw'r rhestr enwau dalennau sy'n cynnwys y data rydych chi am ei ddefnyddio;
  • E2 yw'r meini prawf rydych chi eu heisiau yn seiliedig.

2. Os oes angen rhestru nifer o daflenni gwaith, gallwch ddarllen yr erthygl hon Sut i Restru Enwau Taflen Waith yn Excel? i ddelio â'r dasg hon.

3. Yn Excel, gallwch hefyd ddefnyddio swyddogaeth COUNTIF i ychwanegu'r daflen waith fesul un, gwnewch y fformiwla ganlynol: =COUNTIF(Sheet1!A2:A6,D2)+COUNTIF(Sheet10!A2:A6,D2)+COUNTIF(Sheet15!A2:A6,D2), (Sheet1, Sheet10 ac Sheet15 yw'r taflenni gwaith rydych chi am eu cyfrif, D2 yw'r meini prawf rydych chi'n seiliedig arnyn nhw), ac yna pwyswch Rhowch allwedd i gael y canlyniad. Gweler y screenshot:

doc-count-across-multiple-sheet-7


Cyfrif gwerth penodol ar draws taflenni gwaith lluosog gyda Kutools ar gyfer Excel

Os oes gennych Kutools ar gyfer Excel, Gyda'i Llywio cwarel, gallwch chi restru a chyfrif y gwerth penodol yn gyflym ar draws sawl taflen waith.

Kutools ar gyfer Excel : gyda mwy na 300 o ychwanegiadau Excel defnyddiol, am ddim i geisio heb unrhyw gyfyngiad mewn 30 diwrnod. 

Ar ôl gosod Kutools ar gyfer Excel, prydles gwnewch fel a ganlyn:

1. Cliciwch Kutools > Llywio, gweler y screenshot:

doc-count-across-multiple-sheet-9

doc-count-across-multiple-sheet-10

2. Yn y Llywio cwarel, gwnewch y gweithrediadau canlynol:

(1.) Cliciwch Dod o hyd ac yn ei le botwm i ehangu'r Dod o hyd ac yn ei le cwarel;

(2.) Teipiwch y gwerth penodol i'r Dewch o hyd i beth blwch testun;

(3.) Dewiswch gwmpas chwilio o'r Whithin gollwng, yn yr achos hwn, byddaf yn dewis Taflen Ddethol;

(4.) Yna dewiswch y taflenni rydych chi am gyfrif y gwerthoedd penodol o'r Llyfrau gwaith blwch rhestr;

(5.) Gwiriwch Cydweddwch y gell gyfan os ydych chi am gyfrif mae'r celloedd yn cyfateb yn union;

(6.) Yna cliciwch y Dewch o Hyd i Bawb botwm i restru'r holl werthoedd penodol o daflenni gwaith lluosog, ac mae nifer y celloedd yn cael eu harddangos ar waelod y cwarel.

Dadlwythwch a threial am ddim Kutools ar gyfer Excel Nawr !


Demo: Cyfrif gwerth penodol ar draws taflenni gwaith lluosog gyda Kutools ar gyfer Excel

Kutools ar gyfer Excel: gyda mwy na 300 o ychwanegiadau Excel defnyddiol, am ddim i geisio heb unrhyw gyfyngiad mewn 30 diwrnod. Dadlwythwch a threial am ddim Nawr!


Erthyglau cysylltiedig:

Sut i ddefnyddio countif i gyfrifo'r ganran yn Excel?

Sut i gyfrif gyda meini prawf lluosog yn Excel?

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog    VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr    Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd    Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

Disgrifiad


Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (15)
No ratings yet. Be the first to rate!
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?
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?
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!
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
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?
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.
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.
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")
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
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?
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.
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
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)
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
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations