I haven't had much time to apply all the changes to my spreadsheet that this thread has provoked.

However I didn't like the idea of 01/01/2013 or =DATE(2013,1,1) in the column header to show the month under analysis, I didn't like it being year specific, so I tried =DATE(,1,) and, to my slight surprise as the Help says Year and Day are required, that works - in Excel 2010 at least.

Replying to myself to update this thread and to ask another question.

I said previously that =DATE(,1,) worked. Oh no it didn't. The year it generated was wrong. Anyway I found a different way of achieving what I wanted.

However another little oddity reared its head.

Using the array formula in Ian72's post, I extended it further. That's when I found an apparent oddity.

Basically I wanted 4 conditions to be true so an AND() function seemed appropriate. Entering

=COUNT(IF(AND(YEAR(Dates)=YEAR(J$38),MONTH(Dates)=MONTH(J$38),DegreesF>$T41,DegreesF<$T42),1))

as an array formula didn't work, it always gave a result of 1.

However entering

=COUNT(IF(YEAR(Dates)=YEAR(J$38),IF(MONTH(Dates)=MONTH(J$38),IF(DegreesF>$T41,IF(DegreesF<$T42,1)))))

as an array formula gave me the result I wanted.

Why the difference?

As far as I can see they are logically the same, unless it's something to do with setting it as an array formula.

Any ideas?

The array formula makes it run the function once for each component of the array. However, the AND I think will stop this and it will only actually run once for the whole array. So, with the AND only running once it will always give an answer of 1.

Essentially it has to step through the function for each entry in the array but the AND function stops that from happening as it is not compatible with array formulas.

Thank you kindly

Shouldn't the AND come before all the conditions (i.e. before the IF)?

No, the IF can only take one conditional argument. The AND is to group all 4 conditions together as a single conditional argument to the IF. The logic is correct, it just isn't possible to enumerate an array using AND from what I can tell (I'm sure if MS wanted it to work they could make it but it just isn't the way Excel is built).

Fairy nuff.

I didn't get my head too far into the whole formula. Not easy when you haven't been following the thread.

http://onlinelibrary.wiley.com/doi/10.1002/978111839...

Using multiple IF functions for conditional aggregation: This example applies more complicated conditions with several IF functions. Let�s say we only want to calculate the average difference of the category CX and the difference should be more than eight. You will probably consider using the IF function with the AND function in the array function as shown in cell E3 of Figure G.6. The cell F3 illustrates the array formula applied in the cell E3. The array formula will always return 0. This is because both the AND function and the OR function cannot work properly in array formulas and the input is treated as a series of arguments instead of arrays. This also happens in other aggregation functions, such as SUM, AVERAGE, and COUNT. Thus, you need to consider an alternative approach. In this example, we use multiple IF functions to work for the AND operation.

Fair dos to you. I always double check Excel "that can't be done" statements. Some of the time, there's a trick to making things work.

Fair dos to you. I always double check Excel "that can't be done" statements. Some of the time, there's a trick to making things work.

I must admit I didn't look it up so nice to see some confirmation. My guess was based on how I expected the functions to work (ie how I would probably code it if I was the developer). My intuition for these things is usually not too bad.

Thanks for that, a useful link.

My Bibles on Excel & the various formulae and functions can be read to imply that, but your link explains it much more clearly.

Mind you, I think it could still be argued to be a bug....

Or a design feature....

Bibles? F1