Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Conditional COUNTIF using logicals

Conditional COUNTIF using logicals

Conditional COUNTIF using logicals

I have an array of 8760 hourly data kW readings for a year (24 by 365).  I want to count the number of hours >= 15000 this works but then using and the AND logical it does not work.  Here is formula that does not work  =countif(D4:AA368), (AND>14900, (D4:AA368)<=15000)) it gives me zero.  

Can someone please help me out with this feature?  

RE: Conditional COUNTIF using logicals

Won't the AND function have a problem with the a range comparision?  Could you add an additional column that puts a 1 or 0 in next to the cell, and just reference 2 different cells to create your limit criteria?  For example:

Assume D1 contains the minimum value (14900), and D2 contains the max value (15000).


Then just copy the formula, and sum the results?

RE: Conditional COUNTIF using logicals

CountIf doesn't seem to like logical operators in its Criteria argument.

You could always use a Macro:

Sub Macro1()
Dim c As Range
Dim x As Range
Set x = Sheet1.Range("D4:AA368")
For Each c In x
If c.Value <= 15000 And c.Value > 14900 Then
q = q + 1
End If
Next c
Sheet1.Range("a1").Value = q
End Sub

Good Luck
To get the best from these forums read FAQ731-376 before posting

RE: Conditional COUNTIF using logicals

Use the following as an array formula.


Array function?  Use <crtl>-<shift>-<enter> rather than just <enter> when you have finished typing the formula.  The formula will then appear as if you typed it with {braces} around it.

For greater flexibility, you can put your threshold value of 15000 into a cell and reference that cell in the IF function.


RE: Conditional COUNTIF using logicals

Why not COUNTIF(D4:AA368,">=15000")?  If you want to make the criteria general, say entering 15000 in cell A1, then use COUNTIF(D4:AA368,">="&TEXT(A1,"##")).  This isn't an array formula and you don't need to use Ctrl-shft-enter.

RE: Conditional COUNTIF using logicals

or COUNTIF(D4:AA368,">14900")-COUNTIF(D4:AA368,">15000")


RE: Conditional COUNTIF using logicals

Can also use the frequency function.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close