Solved

using the countif function

Posted on 1999-01-11
8
284 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Expert Comment

by:ShriramS
ID: 1615546
You'll probably have to use the DCount or DCountA functions that are provided
0
 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
This article will show you how to use shortcut menus in the Access run-time environment.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

840 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