Countifs date range not working

[stevencampbellcarter]

Hi Everyone,

I have read a few posts explaining how to fix this but I cannot seem to get it right. Can anyone help?

I want to count the number of entries in a column that fall in a particular month and year. I have been trying this through trying to set 2 date ranges on a countifs formula but i still returns a 0 value. This is my formula so far for May 2013:

=COUNTIFS('Summary sheet'!$M12:$M65536,"=>01/05/2013",'Summary sheet'!AC12:AC65536,""<=31/5/2013)

I have to do this from Jan 2012 to Dec 2014. Does anyone also know how to copy this across while automatically updating the date ranges?

Thanks

Steve

 

[Jonmo1]

Try putting the dates into designated cells, say A1 and A2 for example

=COUNTIFS('Summary sheet'!$M12:$M65536,"=>"&A1,'Summary sheet'!AC12:AC65536,"<="&A2)

A1 = 1/5/2013
A2 = 31/5/2013


You'll need to be more specific on the 2nd part.
Which ranges need to adjust as you drag it accross?

 

[stevencampbellcarter]

Thanks Jonmo,

This still comes up as a 0 value for me. Do you have any other ideas?

I am pressing control - shift - enter but it still puts brackets round the array. Am I missing something really simple?

Thanks

Steve

 

[stevencampbellcarter]

Sorry, I forgot the other part.

What I mean about dragging across is that I want to copy this formula to the right so that I calculate this for multiple months across several years. I would like the dates to update as I copy this across. Is this askig too much?

If there is a way of just asking to to match the dates in the array to a month and year that might be easier.

Regards

Steve

 

[Jonmo1]

Perhaps your dates aren't really dates.

What do these return
=ISNUMBER('Summary sheet'!$M12) <-- filled down to the end
=ISNUMBER('Summary sheet'!$AC12) <-- filled down to the end

 

[stevencampbellcarter]

I have just spotted an error in my formula but it has not solved the issue - I was trying to calculate from two columns when I only wanted to check from column M. But after fixing it I still get the same problem. This is my new formula:

=COUNTIFS('Summary sheet'!$M12:$M139,"=>01/05/2013",'Summary sheet'!M12:M139,""<=31/5/2013)

I have run the =ISNUMBER('Summary sheet'!$M12) you recommended and it shows all the filled in dates as TRUE and all the blanks as FASLE. I have tried to reduce the formula to just take into account a section of the TRUE answers to test it but still get the same result.

I am beginning to think that it is a problem with the arrays as when I press control - shift - enter it shows the { } array error brackets. I have other array formulas that work on this spreadsheet which work and cannot understand what the difference is here.

Sorry to go on, any ideas?

Thanks

Steve

 

[Jonmo1]

You still have syntax error in there
=COUNTIFS('Summary sheet'!$M12:$M139,"=>01/05/2013",'Summary sheet'!M12:M139,""<=31/5/2013)
should be
=COUNTIFS('Summary sheet'!$M12:$M139,"=>01/05/2013",'Summary sheet'!M12:M139,"<=31/5/2013")


But if that's just typoe, I also notice your formula is using dd/mm/yyyy date format.
Maybe your PC is setup to use mm/dd/yyyy.

Does it work this way
=COUNTIFS('Summary sheet'!$M12:$M139,"=>"&DATE(2013,5,1),'Summary sheet'!M12:M139,"<="&DATE(2013,5,31))

 

[stevencampbellcarter]

Thanks, I hadn't spotted that syntax error.


I have tried both and it still shows the { } array error brackets when I press control - shift - enter.


Sorry to keep asking you questions. Do you know what these brackets indicate?


Thanks


Steve

 

[Jonmo1]

Those {brackets} are NOT an indication of an error.
They simply indicate that the formula was entered as an array with CTRL + SHIFT + ENTER

This formula does not require the CTRL+SHIFT+ENTER method.

 

[stevencampbellcarter]

Thanks very much!