SOLVED

CountIFS formula with Asterisk *

Occasional Contributor

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.

 

Sun_H_0-1663114998670.png

 

Thanks in Advance

8 Replies
Column K data are texts or dates? Share a sample workbook.

@Harun24HR 

 

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...

Share link by uploading to onedrive or google drive. In case of comment open full text editor. At the end of editing box you will find an option to attach file. Click browse to select file from your storage disk.
best response confirmed by Sun_H (Occasional Contributor)
Solution

@Sun_H 

Try 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.

@Harun24HR 

 

Thank you. Here is the link, hope it works.

Sample worksheet.xlsx

Oh yes that worked, thank you so much!! I thought it might have had something to do with the cell content! So very much appreciated!
Is there a way I can use this formula without specifying the year?

(I want to be able to duplicate these for each year without having to fix up the formulae for each month every year...)

@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.