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]
[Home]

LVL 9
Who is Participating?

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
Else
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
Else
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
Next
End Function
Good Luck!
0

Author Commented:
Edited text of question
0

Author Commented:
Edited text of question
0

Commented:
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!!
0

Commented:
You'll probably have to use the DCount or DCountA functions that are provided
0

Author 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.
0

Author Commented:
Tried using the DCount & DCountA functions but they don't seem to accept 2 criteria either.
0

Author Commented:
Jboddy

work unfortunatley I have something similar in my spreadsheet
now , and I wanted to change the formular to reduce the file size.

0
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.