Thread: 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)