countif formula

[archivesgirl]

=COUNTIF('mail-data'!D21000, ">12/01/2005and<01/01/2006")

I've been trying to count a cell range that contains data on another
sheet 'mail-data', where the date is bigger than 1 december 2005 and
smaller than 1 january 2006. I keep getting error messages.

help would be appreciated.
Thanks
ArchivesGirl

 

[vezerid]

ArchivesGirl

The COUNTIF() function accepts only primitive level conditions such as:

=COUNTIF('mail-data'!D21000, ">6")

For more complex criteria we use virtual arrays:

=SUMPRODUCT(--('mail-data'!D21000>DATE(2005,12,1))*--('mail-data'!D21000<DATE(2006,12,1)))

As you see we mutliply two arrays, which are based on D2100 yet are
virtual, producing a 0 or 1 in the positions of dates satisfying the
criterion. When a date is in dec2006 it satisfies both conditions,
hence the two 1's in this position multiplied give us 1 instead of 0,
which happens in all other cases. Summing the 1's gives you the count.

HTH
Kostis Vezerides

 

[archivesgirl]

Thank you very much Vezerid. I've tried it and it works great.

 

[vezerid]

I am glad it worked. I also hope the explanations will help you devise
similar formulas in the future.

Regards
Kostis Vezerides