Countif exclusion formula help

**scsuflyboy** · 12-13-2012, 10:13 PM

I have cells A2:A51, B2:B51, C2:C51, D2:D51 and E2:E51 that will either be blank or have a letter in them. I also have cells L2:L51 that is a number.

In another cell I want to count all of the cells that are >0. However, I want the count to exclude any cells where the corresponding A-E cells contain any letter.

This is what I started working with but can't seem to get very far..I am a novice at this so please don't laugh...

=IF( AND(L2:L51 >=1, B2:B51 = "",C2:C51= "", D2:D51 ="",F2:F51="")), COUNTIF(L2:L51))

**vlady** · 12-13-2012, 10:23 PM

since you did not indicate what the xl ver you are using..in later xl
countifs could do that

=countifs( L2:L51,">0",B2:B51,"",C2:C51,"",D2:D51,"",F2:F51,"")

**scsuflyboy** · 12-13-2012, 10:39 PM

Sorry, Excel 2003

**vlady** · 12-13-2012, 10:47 PM

try sumproduct then

=SUMPRODUCT(--(L2:L51>0),--(B2:B51=""),--(C2:C51=""),--(D2:D51=""),--(F2:F51=""))
if the output is not the expected you can upload a sample file. with expected outcome.