Sep 13 2022 05:37 PM - edited Sep 13 2022 08:27 PM
Hi all,
Would appreciate your help with this formula containing CountIFS with *
I want to count the number of cells containing "Campus" and "July" and then "CED" and July". For some reason the count keep coming up as 0 (screen shot below with formula and cells highlighted in yellow).
Any dates within the month should be counted. I've been counting them manually but want to automate the process.
Thanks in Advance
Sep 13 2022 08:50 PM
Sep 13 2022 09:22 PM
Colum K is dates, in the given date format..
How do I attach a file please? Can't seem to find an option for it...
Sep 13 2022 10:09 PM
Sep 14 2022 12:58 AM - edited Sep 14 2022 01:00 AM
SolutionTry this one:
=COUNTIFS(B:B,"Campus",K:K,">="&DATE(2022,7,1),K:K,"<="&DATE(2022,7,31))
Column K contains real dates. Excel stores dates as sequential numbers starting at 1 on January 1, 1900. Although you have formatted the date to display as shown in the picture, the cell does not contain a text like "July 2022". You need to use proper date values (lower and upper boundary) to find dates in July 2022.
Example attached.
Sep 14 2022 04:10 AM
Sep 14 2022 04:19 AM
Sep 15 2022 04:26 PM
Sep 15 2022 07:46 PM - edited Sep 15 2022 09:44 PM
@Sun_H You can enter the month, last day of the month and year in separate cells and refer to these rather then hard coding them in each formula.
Edit: I've now attached a file demonstrating how you could make your file more dynamic.
Sep 14 2022 12:58 AM - edited Sep 14 2022 01:00 AM
SolutionTry this one:
=COUNTIFS(B:B,"Campus",K:K,">="&DATE(2022,7,1),K:K,"<="&DATE(2022,7,31))
Column K contains real dates. Excel stores dates as sequential numbers starting at 1 on January 1, 1900. Although you have formatted the date to display as shown in the picture, the cell does not contain a text like "July 2022". You need to use proper date values (lower and upper boundary) to find dates in July 2022.
Example attached.