Count Diff... I think!

jeffmoseler

Well-known Member
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?

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
jeffmoseler said:
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.

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

Re: Try this

jeffmoseler said:
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

3

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

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?

Re: 3

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

jeffmoseler said:
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 "#".

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?

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

Replies
1
Views
115
Replies
17
Views
475
Replies
1
Views
219
Replies
1
Views
124
Replies
0
Views
254

Threads
1,207,094
Messages
6,076,550
Members
446,212
Latest member
KJAYPAL200

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back