# Excel 2007 : Countif with an or condition

1. ## Countif with an or condition

Hello everyone,

I have a question reguarding a small project im working on. I have weather data from our local stats site and it give me hourly data between jan 2010 to may 20 2011.
The file contains some data in a colum that looks like the following:

cloudy
cloudy and rainy
fog and rainy
fog
haze
cloudy and snow
clear
etc... (another several thousand lines)

Now what I would like to do is determine how many time something other then clear comes up. For example I would like to:

COUNTIF(A1:A50,OR("*rain*","*cloudy*",etc))

Now the above OR is not working, if i remove the OR and use a single criteria I get the proper result, but when i add the OR and another criteria as shown above i get 0 as a result. Notice that some lines of data contain both key words cloudy and rain so this is the reason i want to use the OR, i dont want to count both instances just one for that particular hour.

Matt

2. ## Re: Countif with an or condition

Try this:

=SUMPRODUCT(COUNTIF(A1:A50, B1:B10))

where in A column is your data, and in B column your criterias

3. ## Re: Countif with an or condition

.....but that will double count rows that contain both "rain" and "cloudy" if you use wildcards with the criteria. Can't you just count lines which explicitly aren't "Clear" e.g.

=COUNTIF(A1:A50,"<>Clear")

or

=COUNTIF(A1:A50,"<>*Clear*")

4. ## Re: Countif with an or condition

I could count clear but there are more criteria then the ones I listed so I do need to do something similar to my first post without the double counting of lines as noted above.
I assume countif and the or operations do not work together?

5. ## Re: Countif with an or condition

Check my above solution...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1