Solved

using the countif function

Posted on 1999-01-11
8
282 Views
Last Modified: 2006-11-17
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]

Copyrights © 1996-1998 Experts Exchange, Inc. - Patent Pending
0
Comment
Question by:antrat
8 Comments
 
LVL 9

Author Comment

by:antrat
ID: 1615543
Edited text of question
0
 
LVL 9

Author Comment

by:antrat
ID: 1615544
Edited text of question
0
 
LVL 5

Expert Comment

by:jboddy
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

by:ShriramS
ID: 1615546
You'll probably have to use the DCount or DCountA functions that are provided
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 9

Author Comment

by:antrat
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

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

Author Comment

by:antrat
ID: 1615549
Jboddy

        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.

0
 
LVL 5

Accepted Solution

by:
vboukhar earned 50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Break numbering between parenthesis into a new line 32 56
Office 2010 repair wrong version 5 55
Getting product key for Office 2011 for Mac 9 193
Need a poor man's PowerPoint 5 73
Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now