Question COUNTDIFF IF Criteria ( MrExcel Message Board Excel Questions ) Updated: 20090206 22:35:13 (10) 

COUNTDIFF IF Criteria
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??


Answers: COUNTDIFF IF Criteria ( MrExcel Message Board Excel Questions ) 

COUNTDIFF IF Criteria
Your question is not very clear; it may help if you post a clear concise example.
A guess follows
=IF(SUMPRODUCT((C2:C28="Y"),(E2:E28="Copper"))>=1,1,0)
Dave Patton


COUNTDIFF IF Criteria
Hi Dave,
Sorry.. just got html maker setup  slightly modified my sheet to simplify the post.
Your formula works but in the interest of my learning do you know what is wrong with my countdiff formula?....
Thank you massively fo your time
Reef
Microsoft Excel  12.xls  ___Running: 11.0 : OS = Windows XP 
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout  
 
 A  B  C  D  E  F  1   Bulk  Product Picked (Y/N)   Required  Copper   2   Product1  Y     3   Product2  Y     4   Product3  Y   Copper   5   Product4  Y     6   Product5  Y     7   Product6  Y     8   Product7  Y   Copper   9   Product8  Y     10   Product9  Y     11   Product10  Y     12   Product11  Y     13   Product12  Y     14   Product13  Y     15   Product14  Y     16   Product15  Y     17   Product20  Y     18   Product21  Y     19   Product22  Y     20   Product23  Y     21   Product24  Y     22   Product25  Y     23   Product26  Y     24   Product27  Y     25     Count Total  2   26     Required (1/0)  1   27     Methods  Copper   28     Formula Testing  2   29     Dave Patton's Formula  1   
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Reef


COUNTDIFF IF Criteria
CountDiff is invoked in order to obtain a distinct count, either (multi)conditional or unconditional. What is your objective?
Aladin Akyurek


COUNTDIFF IF Criteria
Hi Aladin Akyurek,
My aim is two fold....
1. The next step is to deal with more metals than just copper in column E. Hence the Countdiff.
2. To use Y or N values in column C to "what if" the products picked. As the products picked changes we can see what test methods will be needed.
Hope you can help
Many Thanks
Reef
Reef


COUNTDIFF IF Criteria
Quote:
Originally Posted by Reef
Hi Aladin Akyurek,
My aim is two fold....
1. The next step is to deal with more metals than just copper in column E. Hence the Countdiff.
2. To use Y or N values in column C to "what if" the products picked. As the products picked changes we can see what test methods will be needed.
Hope you can help
Many Thanks
Reef

The question is: Are you interested in distinct count or not?
Did you do a search on COUNTDIFF on this site? A few posts would certainly show up...
Aladin Akyurek


COUNTDIFF IF Criteria
Aladin Akyurek
I done a lot of reading of the board... this is where I found the Countdiff function in the first place.
My original post was asking about combining the countdiff function with the if function.
The logic looks right from the reading I have done on the board but for some reason it doesn't seem to be working.
Do you see any mistakes I have made in this formula?
{=COUNTDIFF(IF(C2:C28="Y",E2:E28),FALSE)}
Thanks for your time.
Reef
Reef


COUNTDIFF IF Criteria
a) Did you read the Help and Syntax provided with COUNTDIFF?
Counts the number of unique values in a range or an array.
SYNTAX : =COUNTDIFF(Array,Blanks,Exclude) Your formula doesn't follow the defined Syntax.
b) The Array formula {=SUM(IF($C$2:$C$24="Y",IF(E$2:E$24=E$27,1,0)))}
and the SumProduct yield the same answer. The formula
=IF(SUMPRODUCT((C2:C28="Y"),(E2:E28="Copper"))>=1,1,0) includes the "IF" part because you stated "The aim is for the output to be....
"1" if any product with Copper in it is selected (with a "Y" in column B) "
Dave Patton


COUNTDIFF IF Criteria
Hey Dave,
Yeah.. read the support info. I'm obviously missing something in trying to embed the IF into the COUNTDIFF.
I like your SUMPRODUCT idea. Think it will be fine if I don't try to include more than one type of metal test in any one column.
Will run with your SUMPRODUCT but hopefully can get my head around combining the COUNTDIFF & IF statements for future use.
Thanks for your help Dave.
Reef
Reef


COUNTDIFF IF Criteria
=COUNTDIFF(IF(C2:C28="Y",E2:E28),FALSE,FALSE)
Gives number of Unique with "Picked" = "Y"
a) Copper and Alum = 2
b) Copper and blanks evaluates to Copper and 0 =2
You could consider a Pivot Table; an actual Pivot Table looks
much better than the following
Count of Required Required
Picked Alum Copper Zinc Grand Total
N 1 19 20
Y 4 2 6
Grand Total 5 2 19 26
Dave Patton


COUNTDIFF IF Criteria
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
Reef


 Source:  Previous Question: MrExcel Message Board Excel Questions  Next Question: MrExcel Message Board Excel Questions 

