countif formula
 

=COUNTIF('mail-data'!D2:D1000, "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


--
archivesgirl
------------------------------------------------------------------------
archivesgirl's Profile: http://www.excelforum.com/member.php...o&userid=31827
View this thread: http://www.excelforum.com/showthread...hreadid=515511

countif formula
 

ArchivesGirl

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

=COUNTIF('mail-data'!D2:D1000, "6")

For more complex criteria we use virtual arrays:

=SUMPRODUCT(--('mail-data'!D2:D1000DATE(2005,12,1))*--('mail-data'!D2:D1000<DATE(2006,12,1)))

As you see we mutliply two arrays, which are based on D2:D100 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

countif formula
 

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


--
archivesgirl
------------------------------------------------------------------------
archivesgirl's Profile: http://www.excelforum.com/member.php...o&userid=31827
View this thread: http://www.excelforum.com/showthread...hreadid=515511

countif formula
 

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

Regards
Kostis Vezerides