Excel 2007 : Countif with an or condition

**matt1234** · 05-28-2011, 10:14 AM

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.

Please advise, thank you for your help.

Matt

**zbor** · 05-28-2011, 10:18 AM

Try this:

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

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

**daddylonglegs** · 05-28-2011, 10:23 AM

.....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*")

**matt1234** · 05-28-2011, 09:04 PM

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?
Thanks in advance