Home  |  About  | Last |  Submit  |  Contact
AllQuests.com



Previous Question:  shade 2 alternative rows  Excel QuestionsNext Question:  Vlookup  Excel Questions
Question COUNTDIFF IF Criteria ( MrExcel Message Board Excel Questions )
Updated: 2009-02-06 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
 BulkProduct Picked (Y/N) Required - Copper 
2
 Product-1Y   
3
 Product-2Y   
4
 Product-3Y Copper 
5
 Product-4Y   
6
 Product-5Y   
7
 Product-6Y   
8
 Product-7Y Copper 
9
 Product-8Y   
10
 Product-9Y   
11
 Product-10Y   
12
 Product-11Y   
13
 Product-12Y   
14
 Product-13Y   
15
 Product-14Y   
16
 Product-15Y   
17
 Product-20Y   
18
 Product-21Y   
19
 Product-22Y   
20
 Product-23Y   
21
 Product-24Y   
22
 Product-25Y   
23
 Product-26Y   
24
 Product-27Y   
25
   Count Total2 
26
   Required (1/0)1 
27
   MethodsCopper 
28
   Formula Testing2 
29
   Dave Patton's Formula1 
Sheet1 

[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 un-conditional. 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

Previous Question:  shade 2 alternative rows  MrExcel Message Board  Excel QuestionsNext Question:  Vlookup  MrExcel Message Board  Excel Questions

- Source: COUNTDIFF IF Criteria MrExcel Message Board Excel Questions
- Previous Question: shade 2 alternative rows MrExcel Message Board Excel Questions
- Next Question: Vlookup MrExcel Message Board Excel Questions