Need help with nested AND or IF in a count

[wmatthai]

So I have been trying different COUNTIF and DCOUNT, and SUM(IF and I am at wits end... it seems so simple, yet if it was in a DB I would have it figured out in seconds...

So Here is my issue

I need a formula that First looks for values in C9:C18 that have any type of data AND has a string value of "Not Run" in L9:L18

So I first started with =COUNTIF(C9:C18,"="&"*") Works great for the first part
Then =COUNTIF(L9:L18,"Not Run") works great for second part... but cant seem to figure out how to nest and AND in there. I tried +COUNTIF(AND( and that didnt work...

Then I played around with DCOUNT(C9:L18,"Not Run" and that wouldnt pull back the right data either. Most likely my inexperience with the syntax

Lastly I tried =COUNT(IF(C9:C18="*",1,0),IF(L9:L18="Not Run",1,0))

I seriously think I am confusing myself now and would be very happy if you pointed me in the right direction...

Thanks

Will

 

[VoG]

Try

=SUMPRODUCT(--(C9:C18<>""),--(L9:L18="Not Run"))

 

[wmatthai]

You sir are the man, If you are ever around the DC area beer is on me!

 

[AlphaFrog]

Try something like this...
=SUMPRODUCT((C9:C18<>"")*(L9:L18="Not Run"))

Or this if you have Excel 2007 or later
=COUNTIFS(C9:C18,"<>",L9:L18,"Not Run")