Home  |  About  | Last |  Submit  |  Contact

 Tweet

 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
 E25E26E28E29 =

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...