• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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
1 Solution
antratAuthor Commented:
Edited text of question
antratAuthor Commented:
Edited text of question
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!!
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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.

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!

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now