COUNTDIFF & IF Criteria

[Reef]

Hey Board,

I've been playing with the COUNTDIFF function (Morefunc) and find it great.

Now trying to nest an IF statement into the formula but having some trouble.

Hope someone could spare me some time to help with this?

Reef.

Data:
Column B contains Product Codes
Column C contains Y or N ... referring to yes or no.... whether the product has been selected
Column E refers to whether Copper is in the formula...it's on a few rows & blanks in the others

Formula: {=COUNTDIFF($E$2:$E$28,FALSE)}
Result: =1 .....shows that Copper is in the column.... Good.

Problem:
I need to countdiff only the rows in E2 to E28 that have a "Y" in C2 to C28.

I've tried {=COUNTDIFF(IF(C2:C28="Y",E2:E28),FALSE)} but it results in "2" if the two entries for copper have a "Y" next to them.

The aim is for the output to be....
"1" if any product with Copper in it is selected (with a "Y" in column B)
& "0" if I change the "Y"s to "N"s in column B.

Man... any ideas guys??

 

[Reef]

Hey Dave,

I'm with you mate.

I'd thought the first of the two FALSE statements in your COUNTDIFF formula would stop the "0" from being counted. =COUNTDIFF(IF(C2:C28="Y",E2:E28),FALSE,FALSE)

b) Copper and blanks evaluates to Copper and 0 =2....
............"2" is what I'm getting but had expected "1".

Dave, can you see an adjustment that would force the "blanks" to be ignored?

Great stuff... looks so close to what is needed.

I guess =(COUNTDIFF(IF(C2:C28="Y",E2:E28),FALSE,FALSE)-1) is an option but it doesn't really harness COUNTDIFF very cleanly.

Reef