SOLVED

countif

%3CLINGO-SUB%20id%3D%22lingo-sub-1576128%22%20slang%3D%22en-US%22%3Ecountif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576128%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%20I%20would%20like%20to%20count%20the%20cells%20of%20a%20coloumn%20if%20the%20date%20in%20the%20cell%20is%20within%20a%20specific%20month.%3C%2FP%3E%3CP%3Efor%20istance%2C%20I%20may%20have%205%20cells%20with%20a%20date%20within%20January%202020%2C%207%20cells%20February%202020%2C%2015%20cells%20April%202020%20and%20so%20on.%3C%2FP%3E%3CP%3EI%20tried%20with%20criteria%20like%20%22jan%22%20but%20it%20doesn't%20work%3A%20could%20someone%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1576128%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576142%22%20slang%3D%22en-US%22%3ERe%3A%20countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576142%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F753770%22%20target%3D%22_blank%22%3E%40Massimo_Cotichella%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAn%20easy%20way%20to%20do%20this%20is%20by%20creating%20a%20pivot%20table%20based%20on%20your%20data.%3C%2FP%3E%0A%3CP%3EAdd%20the%20date%20field%20(column)%20to%20both%20the%20Rows%20area%20and%20the%20Values%20area.%3C%2FP%3E%0A%3CP%3EExcel%20will%20automatically%20group%20the%20dates%20by%20months.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20prefer%20to%20use%20formulas%2C%20I'd%20add%20a%20helper%20column%20with%20formulas%20of%20the%20form%3C%2FP%3E%0A%3CP%3E%3DMONTH(...)%3C%2FP%3E%0A%3CP%3EThis%20will%20return%201%20for%20January%2C%202%20for%20February%20etc.%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20use%20%3DCOUNTIF(helper_column%2C%201)%20to%20count%20the%20number%20of%20dates%20in%20January%2C%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576222%22%20slang%3D%22de-DE%22%3ESubject%3A%20countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576222%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F753770%22%20target%3D%22_blank%22%3E%40Massimo_Cotichella%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EAttached%20is%20a%20sheet%20with%20an%20example%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNT%20(F%3AF%3B%22%20%26gt%3B%3D%22%20%26amp%3BB1)-COUNT%20(F%3AF%3B%22%20%26gt%3B%22%20%26amp%3BC1)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ETranslated%20into%20English%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3DCOUNTIF(F%3AF%2C%22%26gt%3B%3D%22%20%26amp%3BB1)-COUNTIF(F%3AF%2C%26gt%3B%22%20%26amp%3BC1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Beware%20of%20scammers%20posting%20fake%20support%20numbers%20here.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1592377%22%20slang%3D%22en-US%22%3ERe%3A%20countif%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1592377%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20Hans!%20I%20solved%20my%20problem%20by%20your%20suggestion!%3C%2FP%3E%3CP%3EMassimo%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi! I would like to count the cells of a coloumn if the date in the cell is within a specific month.

for istance, I may have 5 cells with a date within January 2020, 7 cells February 2020, 15 cells April 2020 and so on.

I tried with criteria like "jan" but it doesn't work: could someone help?

3 Replies
Highlighted
Best Response confirmed by Massimo_Cotichella (New Contributor)
Solution

@Massimo_Cotichella 

An easy way to do this is by creating a pivot table based on your data.

Add the date field (column) to both the Rows area and the Values area.

Excel will automatically group the dates by months.

 

If you prefer to use formulas, I'd add a helper column with formulas of the form

=MONTH(...)

This will return 1 for January, 2 for February etc.

You can then use =COUNTIF(helper_column, 1) to count the number of dates in January, etc.

Highlighted

@Massimo_Cotichella 

 

Attached is a sheet with an example

 

=ZÄHLENWENN(F:F;">=" &B1)-ZÄHLENWENN(F:F;">" &C1)

Translated into English

=COUNTIF(F:F,">=" &B1)-COUNTIF(F:F,">" &C1)

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

* Beware of scammers posting fake support numbers here.

Highlighted

@Hans Vogelaar 

thank you Hans! I solved my problem by your suggestion!

Massimo