# Need help with nested AND or IF in a count

#### wmatthai

##### New Member
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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Try

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

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

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

Replies
6
Views
657
Replies
1
Views
250
Replies
4
Views
280
Replies
5
Views
200
Replies
6
Views
372

1,217,356
Messages
6,136,079
Members
449,988
Latest member
Mabbas

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back