Count Diff... I think!

[jeffmoseler]

I need a COUNTDIFF command written that looks like this:

COUNTDIFF( if g3 is equal to 10 and k3 is unique)

How would I do that?

 

[Brian from Maui]

I need a COUNTDIFF command written that looks like this:

COUNTDIFF( if g3 is equal to 10 and k3 is unique)

How would I do that?

Can you post some sample data? COUNTDIFF returns the number of unique values in a range.

 

[jeffmoseler]

Try this

OK Sometimes I'm not very good at describing what I need. I think this will be clearer.

Column G Column K
10 A
10 B
5 C
6 C
7 E
10 B
4 H
4 W
10 L

I need the COUNTDIFF formula to say:

if Column G is 10 and Column K is Unique then count the cell in Column K

ie: the result from above would be 3

 

[Brian from Maui]

Re: Try this

OK Sometimes I'm not very good at describing what I need. I think this will be clearer.

Column G Column K
10 A
10 B
5 C
6 C
7 E
10 B
4 H
4 W
10 L

I need the COUNTDIFF formula to say:

if Column G is 10 and Column K is Unique then count the cell in Column K

ie: the result from above would be 3

Not 2?

=IF(AND(A1=10,COUNTIF($B$1:$B$9,B1)=1),1,0)

and drag down, then sum

 

[jeffmoseler]

3

A, B, and L are the three values in Column K that are unique, so the answer would be 3.

 

[jeffmoseler]

This is the formula that I have come up with:

=COUNTDIFF(IF(MONTH('Copy 2'!G3:G1000)=10,'Copy 2'!K3:K1000,1),FALSE,2)

but it keeps giving me a false on the if statement. I'm guessing that it is having a hard time doing the MONTH statement on a whole column. Is there anyway around this?

 

[Aladin Akyurek]

Re: 3

A, B, and L are the three values in Column K that are unique, so the answer would be 3.

=COUNTDIFF(IF(G3:G11=10,K3:K11,0),FALSE,0)

which you need to confirm with control+shift+enter.

See also my reply to your last post.

 

[Aladin Akyurek]

This is the formula that I have come up with:

=COUNTDIFF(IF(MONTH('Copy 2'!G3:G1000)=10,'Copy 2'!K3:K1000,1),FALSE,2)

but it keeps giving me a false on the if statement. I'm guessing that it is having a hard time doing the MONTH statement on a whole column. Is there anyway around this?

Let X2 house the first day date of the month/year of interest, say, 1-Oct-05.

You can the invoke:

=COUNTDIFF(IF('Copy 2'!G3:G1000-DAY('Copy 2'!G3:G1000)+1=X2),'Copy 2'!K3:K1000,0),FALSE,0)

which must be confirmed with control+shift+enter.

BTW, we can replace the 0's with a different value like "#".

 

[jeffmoseler]

I don't understand why this is not working
Here is some sample data

Column E-----Column F
1/27/2005 -----1
10/28/2005-----1
10/29/2005-----1
10/30/2005-----1
10/31/2005-----1
11/1/2005 -----1
11/2/2005 -----1
11/3/2005 -----1
11/4/2005 -----1

=SUM(IF(MONTH(E2:E9)=10,F2:F9,0))

This formula returns a 0. I tried the control+shift+enter thing, but it doesn't do anything. Either way, the result should be 4, shouldn't it?

 

[Aladin Akyurek]

I don't understand why this is not working
Here is some sample data

Column E-----Column F
1/27/2005 -----1
10/28/2005-----1
10/29/2005-----1
10/30/2005-----1
10/31/2005-----1
11/1/2005 -----1
11/2/2005 -----1
11/3/2005 -----1
11/4/2005 -----1

=SUM(IF(MONTH(E2:E9)=10,F2:F9,0))

This formula returns a 0. I tried the control+shift+enter thing, but it doesn't do anything. Either way, the result should be 4, shouldn't it?

I thought you were trying to obtain a conditional unique count. Now it looks like you are after a conditional sum...

Let A2 house the first day date of the month/year of interest: 1-Oct-05.

Now invoke:

=SUMPRODUCT(--(E2:E9-DAY(E2:E9)+1=A2),F2:F9)

which just needs enter.