Conas Countif a dhéanamh de réir dáta / míosa / bliana agus raon dáta in Excel?

Mar shampla, tá uainchlár ballraíochta againn lena lá breithe, agus anois táimid ag ullmhú chun cártaí lá breithe a dhéanamh do na baill seo. Sula ndéantar na cártaí lá breithe, ní mór dúinn a áireamh cé mhéad breithlá atá i mí / bliain / dáta ar leith. Anseo treoróidh mé tú chuig Countif de réir dáta / míosa / bliana agus raon dáta le foirmlí in Excel leis na modhanna seo a leanas:

Líon de réir míosa / bliana ar leith le foirmlí in Excel
Líon de réir bliana sonraithe nó raon dáta in Excel

Líon de réir míosa / bliana agus raon dáta ar leith le foirmlí in Excel

Sa chuid seo, tabharfaidh mé isteach roinnt foirmlí chun breithlaethanta a chomhaireamh faoi mhí, bliain nó raon dáta áirithe in Excel.

Countif faoi mhí áirithe

Má cheaptar go bhfuil tú chun breithlaethanta a chomhaireamh atá i mí shonrach 8, is féidir leat an fhoirmle seo a iontráil i gcill bhán, agus ansin an Iontráil eochair.

= SUMPRODUCT (1 * (MÍ (C3: C16) = G2))

Nótaí:

San fhoirmle thuas, is é C3: C16 an colún Dáta Breithe sonraithe a mbeidh tú ag comhaireamh breithlaethanta ann, agus is é G2 an chill leis an uimhir mhí shonrach.

Is féidir leat na foirmlí eagar seo a chur i bhfeidhm freisin = SUM (IF (MÍ (B2: B15) = 8,1)) (brúigh eochracha Ctrl + Shift + Iontráil) chun breithlaethanta a chomhaireamh faoin mí ar leith.

Countif faoi bhliain áirithe

Más gá duit na breithlaethanta a chomhaireamh faoi bhliain áirithe, a deir 1988, is féidir leat ceann de na foirmlí thíos a úsáid de réir mar is gá duit.

= SUMPRODUCT (1 * (BLIAIN (C3: C16) = 1988))

= SUM (IF (BLIAIN (B2: B15) = 1988,1))

Nóta: An dara foirmle foirmle eagar. Cuimhnigh brú ar an Ctrl + Shift + Iontráil eochracha le chéile tar éis dul isteach sna foirmlí;

Líon faoi dháta áirithe

Más gá duit comhaireamh faoi dháta faoi leith (deir 1992-8-16), déan iarratas thíos faoin bhfoirmle, agus brúigh an Iontráil eochair.

=COUNTIF(B2:B15,"1992-8-16")

Líon de réir raon dáta áirithe

Más gá duit a chomhaireamh más déanaí / níos luaithe ná dáta sonrach (deir 1990-1-1), is féidir leat na foirmlí thíos a chur i bhfeidhm:

= COUNTIF (B2: B15, ">" & "1990-1-1")

= COUNTIF (B2: B15, "<" & "1990-1-1")

Chun a áireamh má tá sé idir dhá dháta shonracha (deir idir 1988-1-1 agus 1998-1-1), cuir an fhoirmle seo i bhfeidhm le do thoil:

=COUNTIFS(B2:B15,">"&"1988-1-1",B2:B15,"<"&"1998-1-1")

Tá an fhoirmle ró-chasta le cuimhneamh? Sábháil an fhoirmle mar iontráil Auto Text le haghaidh athúsáid agus gan ach cliceáil amháin sa todhchaí!
Léigh níos mó ... Triail saor in aisce

Áireamh go héasca ag Bliain fhioscach, leathbhliain, uimhir seachtaine, nó lá na seachtaine in Excel

Tá an ghné Grúpáil Ama Speisialta PivotTable, arna sholáthar ag Kutools le haghaidh Excel, in ann colún cúntóra a chur leis chun an bhliain fhioscach, leathbhliain, uimhir seachtaine, nó lá na seachtaine a ríomh bunaithe ar an gcolún dáta sonraithe, agus ligean duit comhaireamh, suim a dhéanamh go héasca , nó meáncholúin bunaithe ar na torthaí ríofa i dTábla Pivot nua. Triail Saor in Aisce Gné Iomlán 30 lá!

Kutools le haghaidh Excel - Cuimsíonn sé níos mó ná 300 uirlis áisiúil le haghaidh Excel. Triail saor in aisce gné iomlán 30 lá, níl aon chárta creidmheasa ag teastáil! Get sé anois

Líon de réir dáta, bliana nó raon dáta sonraithe in Excel

Má tá Kutools for Excel suiteáilte agat, is féidir leat a chuid a chur i bhfeidhm Roghnaigh Cealla Sonracha fóntais chun líon na dtarluithe a chomhaireamh de réir dáta, bliana nó raon dáta sonraithe in Excel go héasca.

Kutools le haghaidh Excel- Cuimsíonn sé níos mó ná 300 uirlis áisiúil le haghaidh Excel. Triail iomlán saor ó ghné 30 lá, níl aon chárta creidmheasa ag teastáil! Get sé anois

1. Roghnaigh an colún lá breithe a mbeidh tú ag brath air, agus cliceáil Kutools > Roghnaigh > Roghnaigh Cealla Sonracha. Féach an pictiúr:

2. Sa bhosca dialóige Oscail Roghnaigh Cealla Sonracha, déan mar a thaispeántar thuas an scáileán:
(1) Sa Cineál roghnúcháin alt, seiceáil rogha amháin de réir mar is gá duit. Inár gcás féin, déanaimid seiceáil ar an Cell rogha;
(2) Sa Cineál sonrach alt, roghnaigh an Níos mó ná nó cothrom le ón gcéad liosta anuas, agus ansin clóscríobh an chéad dáta den raon dáta sonraithe sa bhosca ceart; seo chugainn roghnaigh an Níos lú ná nó cothrom le ón dara liosta anuas, agus clóscríobh an dáta deireanach den raon dáta sonraithe sa bhosca ceart, agus ansin seiceáil agus rogha;
(3) Cliceáil ar an Ok cnaipe.

3. Anois tagann bosca dialóige amach agus taispeánann sé cé mhéad sraitheanna a roghnaíodh mar atá thíos den ghrianghraf a thaispeántar. Cliceáil le do thoil ar an OK cnaipe chun an bosca dialóige seo a dhúnadh.

Nótaí:
(1) Chun líon na dteagmhas a chomhaireamh de réir bliana sonraithe, sonraigh díreach an raon dáta ón gcéad dáta i mbliana go dtí an dáta deireanach i mbliana, amhail ó 1 / 1 / 1990 chun 12 / 31 / 1990.
(2) Chun líon na dteagmhas a chomhaireamh faoi dháta sonraithe, mar shampla 9/14/1985, sonraigh na socruithe sa bhosca dialóige Roghnaigh Cealla Sonracha mar a thaispeántar thíos an scáileán:

Taispeántas: Countif de réir dáta, lá seachtaine, míosa, bliana, nó raon dáta in Excel

Kutools le haghaidh Excel Cuimsíonn sé níos mó ná 300 uirlis áisiúil le haghaidh Excel, saor in aisce le triail gan teorainn i 30 lá. Íoslódáil agus Triail In Aisce Anois!

Earraí gaolmhara:

Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.

Leigh Nios mo... Íoslódáil saor in aisce ... Ceannach ...

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á!

Leigh Nios mo... Íoslódáil saor in aisce ... Ceannach ...

25 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
thiru · 4 months ago
i need report weekly wise like a 7th 14th 21st 28th count only.other days are no need for every month i tried many pivot but i can't find out please help me.
- Reply
To post as a guest, your comment is unpublished.
kellytte · 11 months ago
@Emir In the formula =SUMPRODUCT(1*(MONTH(C3:C16)=G2)), G2 is the specified month number, says 4. If we replace MONTH(C3:C16) with the whole columns (or C3:C16), there is no value that equal to the value 4, therefore we cannot get the count result.
- Reply
To post as a guest, your comment is unpublished.
Emir · 1 years ago
Why we cant justselect whole column, instead of using this MONTH(C3:C16) ?
- Reply
To post as a guest, your comment is unpublished.
Ryan · 1 years ago
@kelly001 Yes, they do - Excel rounds blank cells to January 0, 1900 - therefore when I'm trying to count January months over a set range that has not been filled in yet - I'm getting a higher number for January due to this.
- Reply
To post as a guest, your comment is unpublished.
kellytte · 2 years ago
@Roxie Hi Roxie,
You should try the Compare cells feature, which can compare two columns of cells, find out, and highlight the exactly same cells between them or the differences. https://www.extendoffice.com/product/kutools-for-excel/excel-compare-two-cells-of-equal.html
- Reply
To post as a guest, your comment is unpublished.
Roxie · 2 years ago
What would you do if you have 1 column of events within a date range and need to count if those events had a date in another column?

Example: I have column B as the event dates which vary each month. Column D has the date they came into a consultation. I'm trying to count how many people from that specific event for a date range came to a consultation for any date.
- Reply
To post as a guest, your comment is unpublished.
kellytte · 2 years ago
@RHON Hi Rhon,
Could you describe more about the error? Does the error come out when converting December to 12, or when counting by “12”?
- Reply
To post as a guest, your comment is unpublished.
kellytte · 2 years ago
@mary Hi mary,
In your case, you should count by the specified date. For example, count by the first Sunday of Jan, 2019 (in other words 2019/1/6), you can apply the formula =COUNTIF(E1:E16,"2019/1/6")
- Reply
To post as a guest, your comment is unpublished.
RHON · 2 years ago
JAN = 1
FEB = 2
..
..
DEC = 12 ? NOT CORRECT RESULT IN DECEMBER
- Reply
To post as a guest, your comment is unpublished.
mary · 2 years ago
How can I count a cell on a specific day of the week. For example, I want to find a number of something on the first Sunday of the month
- Reply
To post as a guest, your comment is unpublished.
kellytte · 2 years ago
@Willie Pettigrew Hi Willie,
You can do as follows:
(1) Add a new column and round time data to half hour by the formula =MOD(INT(A2/(1/48))*(1/48),1) (A2 is the time cell)
(2) Select the data range including the new column, create a pivot table.
(3) In the PivotTable Fields pane, set date and the new half hour as Row fields, set the data you will sum or count as Value field.
(4) Turn off the grouping in the PivotTable.
- Reply
To post as a guest, your comment is unpublished.
kellytte · 2 years ago
@IvTe Hi IvTe,
Do you need to count data meeting two conditions? You can use the COUNTIFS function.
- Reply
To post as a guest, your comment is unpublished.
Sommen · 2 years ago
rumus ini = SUMPRODUCT (1 * (YEAR (B2: B15) = 1988)) kalau datanya (range) sampe 20ribu ko ga bisa ya?
- Reply
To post as a guest, your comment is unpublished.
Agata · 2 years ago
Thank you!
- Reply
To post as a guest, your comment is unpublished.
IvTe · 2 years ago
I have a monthly report but need to count only daily data. I don't know to use Today option if formula is =COUNTIF(A1:A50,"RR")=TODAY(). Please help
- Reply
To post as a guest, your comment is unpublished.
Willie Pettigrew · 3 years ago
in Excel 2103 I have a long list of date and times, i am trying to calculate the number between each half hour, e.g. 8am and 8.30am on each date. Can you assist?
- Reply
To post as a guest, your comment is unpublished.
kelly001 · 3 years ago
@deepak Hi Deepak,
Just add the sheet name before referenced cells. For example your data is in Sheet1, and you want to count the sales in Feb, you can modify the formula as =SUMPRODUCT(1*(MONTH(Sheet1!B2:B15)=2))
- Reply
To post as a guest, your comment is unpublished.
deepak · 3 years ago
@kelly001 i have on sheet where in daily sales add in a single day
08-Feb-18 AMIT SHARMA
08-Feb-18 AVINASH KUMAR
08-Feb-18 SANJAY SEHGAL
09-Feb-18 hay
09-Feb-18 ravi

but i need on second sheet
08-Feb-18 3
09-Feb-18 2

so pleaase tell me farumula for this counts total sales at other sheet
- Reply
To post as a guest, your comment is unpublished.
SANJU · 3 years ago
@deepak =A2-A1+1
OR
=END DATE - START DATE + 1
- Reply
To post as a guest, your comment is unpublished.
kelly001 · 3 years ago
@jimmy Hi jimmy,
Blank cells won’t affect the calculation result of this formula =SUMPRODUCT(1*(MONTH(B2:B15)=8))
- Reply
To post as a guest, your comment is unpublished.
jimmy · 3 years ago
=SUMPRODUCT(1*(MONTH(B2:B15)=8))

I need to add 'exclude blank cells', please help.
- Reply
To post as a guest, your comment is unpublished.
deepak · 4 years ago
I WANT ANSWER 1 DAY( WITH EXAMPLE FORMULA)
COUNT BETWEEN :- 01/01/2004 TO 01/01/2004 = 1 DAY
WHICH FORMULA WILL APPLY
- Reply
To post as a guest, your comment is unpublished.
TrevorC · 5 years ago
I'm trying to create a table that counts the number of times a device has been used each month from another table which shows the date in cell a and then in cell b,c,d is a 1 if that device is being used on that date. So, on Sheet 2, is a table with Date (A1) Device 1 (B1) Device 2 (C1) Device 3 (D1). I would enter the date 01/01/16 in cell A1 and lets say Device 3 is being used so I would put a '1' in cell D1. on 02/01/16 device 2 is used so I would enter the date and put a '1' in cell C1. So on and so forth.
On Sheet 1, is a summary table which tells me how many times Device 1 has been used each month. So, in A2 I have the Month 'January', A3 'February' etc, in cell B2, Device 1, Cell C2, Device 2 etc. I want a total count. Is that possible?
- Reply
To post as a guest, your comment is unpublished.
L · 5 years ago
I am using =COUNTIFS(E2:E900,">"&"6/1/2015",E2:E900,"
- Reply
To post as a guest, your comment is unpublished.
Ross · 5 years ago
Hi

I am trying to use a formula to count how many dates are passed a changing date, e.g TODAY()-330.
I need this to continue to change as it is for expiring training courses

What I have been using is =COUNTIFS(C3:C28,"
- Reply

Logáil isteach do chuntas

Athshocraigh Pasfhocal