[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# using the countif function

Posted on 1999-01-11
Medium Priority
290 Views
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]

0
Question by:antrat
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 9

Author Comment

ID: 1615543
Edited text of question
0

LVL 9

Author Comment

ID: 1615544
Edited text of question
0

LVL 5

Expert Comment

ID: 1615545
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

LVL 1

Expert Comment

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

LVL 9

Author Comment

ID: 1615547
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

LVL 9

Author Comment

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

LVL 9

Author Comment

ID: 1615549
Jboddy

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

0

LVL 5

Accepted Solution

vboukhar earned 200 total points
ID: 1615550
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
New style of hardware planning for Microsoft Exchange server.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
###### Suggested Courses
Course of the Month13 days, 4 hours left to enroll