COUNTIF function Using Dates

If you're using XL2007 as your profile implies you can make use of the COUNTIFS function.

=COUNTIFS(B1:B300,">="&A1-7,B1:B300,"<="&A1-1)

if not (re: 2007) you're looking at SUMPRODUCT but the above is more efficient.

Yes, COUNTIFS was introduced in XL2007 along with the likes of SUMIFS, AVERAGEIF, AVERAGEIFS - none are thus backwards compatible with earlier versions of XL - in earlier versions you would be looking at either SUMPRODUCT or Arrays to conduct multiple criteria aggregations.

OK but for ref.

=COUNTIFS(B1:B300,">="&A1-7,B1:B300,"<="&A1-1)

could be replaced by

=SUMPRODUCT(--(ROUNDUP((B1:B300-A1)/7,0)=-1))

If you want to do more complex date calcs (ie other than 1 week prior) you can use a similar construct to the COUNTIFS

=SUMPRODUCT(--(B1:B300>=A1-7),--(B1:B300<=A1-1))

the latter is the more intuitive approach (in addition to being more flexible)

In all honesty the SUMPRODUCT link in my sig. is about as good as you will/can get... I wouldn't/don't want to detract from it with my own ramblings on the subject!

=COUNT(IF(BM!D497:BM!D497>=A3-7,1,0)*IF(BM!D497>BM!D497<=A3-1,1,0))}

not quite sure re: ranges (altered for sake of demo) but you would want either

=COUNT(IF((BM!D497:D1000>=A3-7)*(BM!D497:D1000<=A3-1),1))
CSE

or just

=SUM((BM!D497:D1000>=A3-7)*(BM!D497:D1000<=A3-1))
CSE

(and not my tutorial unfortunately, author is Bob Phillips (MS Excel MVP))

Originally Posted by wbmcwhorter

So in conclusion, with a single array being used, the COUNT function with two IFs should get the job done. And when I need to use two arrays SUMPRODUCT is the only way for me to go...

Not entirely sure I grasp the above but I would put it like this and again I stress this is aimed primarily at those running a version pre XL2007...

If you have a single condition you should adopt standard COUNTIF/SUMIF functions as these are incredibly efficient.

If you have multiple conditions, then first see if you create a singular condition based on helper cells utilising concatenation... if you can do so and revert to the standard COUNTIF & SUMIF functions as outlined already, this approach will be more efficient.

If you can not create a single condition (ie concatenation etc not viable) then you're traditionally left with a choice between:

SUMPRODUCT

and

CSE Array
(CSE being CTRL + SHIFT + ENTER of course)

It's generally accepted that there is little difference between the two in terms of efficiency... SUMPRODUCT is processed in the same way as an Array - on that basis neither are quick.

The main advantages of SUMPRODUCT over CSE is the fact it doesn't require CSE (!) so from an end users perspective it is perhaps a little more robust.

CSE Arrays have an advantage over SUMPRODUCT in so far as they are more flexible - eg handling underlying error values in source data etc etc...

If you find yourself conducting multiple SUMPRODUCT formulae - say to create an Average then I would generally say you're better off reverting to a single array, eg instead of

=SUMPRODUCT(--(A1:A100="x"),--(B1:B100="y"),C1:C100)/SUMPRODUCT(--(A1:A100),--(B1:B100="y"))

I would opt for

=AVERAGE(IF((A1:A100="x")*(B1:B100="y"),C1:C100))
CSE

I hope that clears things a little.

(there are also the Database functions like DGET etc but they are seen less often though they are certainly no less efficient, many would argue the opposite but they can be trickier to setup)

There are two methods of coercion yes - multiplication and double unary.

The latter is preferable for many as it is deemed the slightly more efficient coercer and also because it's slightly better at handling non-numerics in any summation range.

However the multiplication method has an advantage over double unary in that it can handle arrays of different dimensions (double unary can not).

Needless to say therefore each has it merits and circumstances often preclude either/or

Your first formula:

Please Login or Register  to view this content.

Implied your committing as an array { } ... this is not required. This is really the fundamental advantage of SUMPRODUCT over an Array - ie does not require CSE.

The above looks ok to me in terms of syntax though I'm curious as to why you're summing the date values ?
Given usage of MONTH & YEAR the above would return an error should any value in F6:F287 be of a type that prevents coercion this is because both functions (MONTH & YEAR) will by their nature coerce the underlying values as though they are dates (numbers) thus if those values are not numeric (text strings etc (including Nulls)) then the above will generate #VALUE! error
Similarly given explicit coercion of the final range regardless of MONTH/YEAR should any value in summation range be non-numeric it too would generate a #VALUE! error

Your second formula

Please Login or Register  to view this content.

Makes more sense in terms of ranges etc (ie use of J rather than F as final array) but the above would of course still generate #VALUE! errors given use of MONTH & YEAR should any values in F be of a type that prevents coercion.

If the #VALUE! error is an issue on occasion (eg formula nulls etc) you can use an alternative (slower) route of

Please Login or Register  to view this content.

If coercion is on the other hand not an issue another option would be:

Please Login or Register  to view this content.

which would be slightly quicker given only one test is conducted

(though the same is true of the TEXT based test the string manipulation would be deemed relatively expensive and thus the MONTH & YEAR tests would be seen as quicker - per Bob Phillips himself - author of white paper)