COUNTDIFF & IF Criteria

Reef · Jan 27, 2009

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

Dave Patton · Jan 27, 2009

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)

Reef · Jan 28, 2009

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

12.xls

A

B

C

D

E

F

1

Bulk

ProductPicked(Y/N)

Required-Copper

2

Product-1

Y

3

Product-2

Y

4

Product-3

Y

Copper

5

Product-4

Y

6

Product-5

Y

7

Product-6

Y

8

Product-7

Y

Copper

9

Product-8

Y

10

Product-9

Y

11

Product-10

Y

12

Product-11

Y

13

Product-12

Y

14

Product-13

Y

15

Product-14

Y

16

Product-15

Y

17

Product-20

Y

18

Product-21

Y

19

Product-22

Y

20

Product-23

Y

21

Product-24

Y

22

Product-25

Y

23

Product-26

Y

24

Product-27

Y

25

CountTotal

2

26

Required(1/0)

1

27

Methods

Copper

28

FormulaTesting

2

29

DavePatton'sFormula

1

Sheet1

Aladin Akyurek · Jan 28, 2009

CountDiff is invoked in order to obtain a distinct count, either (multi)-conditional or un-conditional. What is your objective?

Reef · Jan 28, 2009

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

Aladin Akyurek · Jan 28, 2009

Reef said:
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...

Reef · Jan 28, 2009

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

Dave Patton · Jan 28, 2009

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) "

Reef · Jan 28, 2009

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

Dave Patton · Jan 28, 2009

=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

COUNTDIFF & IF Criteria

Reef

New Member

Excel Facts

Dave Patton

Well-known Member

Reef

New Member

Aladin Akyurek

MrExcel MVP

Reef

New Member

Aladin Akyurek

MrExcel MVP

Reef

New Member

Dave Patton

Well-known Member

Reef

New Member

Dave Patton

Well-known Member

Similar threads

Forum statistics

Share this page

COUNTDIFF & IF Criteria

New Member

Excel Facts

Well-known Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock