using the countif function

when using the countif formula how do I set 2 criteria
eg countif(A1:A800,">B1","<B2"). The data in cells B1 & B2 are dates.

[Cancel Editing]
[How To Use Experts Exchange]

Copyrights © 1996-1998 Experts Exchange, Inc. - Patent Pending
Who is Participating?
vboukharConnect With a Mentor Commented:
You can write your own User defined function to do that. There was same question in experts-exchange some time ago. (for SUMIF function). Look at sample below
Function MyCountIf(WhatSum As Object, R1 As Object, Crit1 As String, R2 As Object, Crit2 As String) As Double
   Col1 = R1.Column
   Col2 = R2.Column
   MyCountIf = 0
   For Each c In WhatSum
     If IsNumeric(Cells(c.Row, Col1)) Then
       MyCheck = Str(Cells(c.Row, Col1)) & Crit1
       TmpOper = Left(Crit1, 1)
       TmpCrit = Right(Crit1, Len(Crit1) - 1)
       MyCheck = Chr(34) & Cells(c.Row, Col1) & Chr(34) & TmpOper & Chr(34) & TmpCrit & Chr(34)
     End If
     IsCrit1 = Evaluate(MyCheck)
     If IsNumeric(Cells(c.Row, Col2)) Then
        MyCheck = Str(Cells(c.Row, Col2)) & Crit2
       TmpOper = Left(Crit2, 1)
       TmpCrit = Right(Crit2, Len(Crit2) - 1)
       MyCheck = Chr(34) & Cells(c.Row, Col2) & Chr(34) & TmpOper & Chr(34) & TmpCrit & Chr(34)
     End If
     IsCrit2 = Evaluate(MyCheck)
     If IsCrit1 And IsCrit2 Then
       MyCountIf = MyCountIf + 1
     End If
End Function
Good Luck!
antratAuthor Commented:
Edited text of question
antratAuthor Commented:
Edited text of question
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

You don't! It only alows for one criteria.
You will have to work around it.
Try using something like this (note the use of x to represent the value of the rows that should be unique for each row)
=IF((Ax>$B$1),Ax,0) in column C
=IF((Ax<$B$2),Ax,0) in column D
=IF(Cx=Dx,1,"") in column E and count the results in column E
Let me know if it is any help!!
You'll probably have to use the DCount or DCountA functions that are provided
antratAuthor Commented:
I've been trying to use the DCOUNT & DCOUNTA functions
but they still don't accept 2 criteria , or maybe I'm not Writing
them properly . Could please give an example based on my
own example.
antratAuthor Commented:
Tried using the DCount & DCountA functions but they don't seem to accept 2 criteria either.
antratAuthor Commented:

        Thanks for your answer. What you have suggested will
work unfortunatley I have something similar in my spreadsheet
now , and I wanted to change the formular to reduce the file size.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.