How to count only unique values from a column in a COUNTIFS

[slam]

I have this formula so far:

=COUNTIFS(Raw!$B:$B,"11/22/2023  12:00:00 AM",Raw!S:S,A24,Raw!W:W,C23)

The problem is that my Raw worksheet has a lot of duplicates, and I only need to count one of each instance from column C on the Raw worksheet. Could someone please show me how to incorporate that into my formula?

Thanks!

 

[RobP]

Hi,

maybe you could use COUNTA and FILTER instead..

I'm not exactly sure which data you want to count, and what values.. but here you see how to first filter your range for all your values, then choose UNIQUE items, then COUNT them

If you are filtering say Range B:W in total, then you add your three criteria in brackets, separated by "*" on each.
Then wrap inside UNIQUE()
Then use COUNTA to see how many results you have..

=COUNTA(UNIQUE(FILTER(B:W,(B:B="11/22/2023")*(S:S=A24)*(W:W=C23))))

Hope it helps guide you at least ..

ROb

 

[Fluff]

How about

=rows(unique(filter(Raw!$B:$B,(Raw!$B:$B="11/22/2023  12:00:00 AM")*(Raw!S:S=A24)*(Raw!W:W=C23))))

 

[slam]

Hi both - thank you for your replies. I have tried both solutions, but have some issues with each. I am not seeing either formula checking Raw!C:C to ensure the same value from this column is not counted twice. Apologies if I did not explain it well. Here is some more info that may help.

Column C is ID numbers

1234
1234
1234
1234
1234
5000
4444
4444
4444
4444
etc

On the Raw worksheet, the other columns I am referencing in the formula (B, S, W) are identical for each ID. If the other criteria matches what I'm looking for in my formula, I only want to increment the count by 1 for the ID 1234, regardless of how many times it appears.

Hi,

maybe you could use COUNTA and FILTER instead..

I'm not exactly sure which data you want to count, and what values.. but here you see how to first filter your range for all your values, then choose UNIQUE items, then COUNT them

If you are filtering say Range B:W in total, then you add your three criteria in brackets, separated by "*" on each.
Then wrap inside UNIQUE()
Then use COUNTA to see how many results you have..

=COUNTA(UNIQUE(FILTER(B:W,(B:B="11/22/2023")*(S:S=A24)*(W:W=C23))))

Hope it helps guide you at least ..

ROb

I've modified this formula to this to restore my sheet references, but the result is 1 for every cell I add it to, which should not be the case. Current formula:

=COUNTA(UNIQUE(FILTER(Raw!$B:$W,(Raw!$B:$B="11/22/2023")*(Raw!$S:$S=$A24)*(Raw!$W:$W=C$23))))

How about

=rows(unique(filter(Raw!$B:$B,(Raw!$B:$B="11/22/2023  12:00:00 AM")*(Raw!S:S=A24)*(Raw!W:W=C23))))

This is giving me a #CALC! error which says Empty Array

 

[Fluff]

This is giving me a #CALC! error

In that case please post some sample data using the XL2BB add-in

 

[slam]

Will try to get some sample data soon. Just a quick update to my explanation. I would look at it like it's almost like I did a Remove duplicates based on Raw!C:C before I used my formula in my first post. Then that formula would work as intended.

 

[slam]

Here is a simplified mock up with all other data removed, and how the output should look. The 2 in the second table needs to come from the formula. It should count each unique ID one time when the other criteria matches. This is Excel 365.

Unfortunately Mini Sheet is making my file crash, so using Table Only, and manually added in the column letters

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
	BatchTimestamp	ID																Status				Org
	11/22/2023 0:00	1341																Carry Over				CHI
	11/22/2023 0:00	1341																Carry Over				CHI
	11/22/2023 0:00	1341																Carry Over				CHI
	11/22/2023 0:00	5000																Carry Over				CHI
	11/22/2023 0:00	5000																Carry Over				CHI

		CHI
Carry Over		2

Thanks!

 

[Fluff]

Thanks for that, how about

=ROWS(UNIQUE(FILTER($B:$B,($B:$B=DATE(2023,11,22))*(S:S=S14)*(W:W=W14))))

 

[slam]

Hi Fluff, thank you, I've got this working. I think your first reference was intended to be C:C. I updated sheet references and gave it an IFERROR condition, because if there was no match, it was giving the CALC error. This is what I now have.

=IFERROR(ROWS(UNIQUE(FILTER(Raw!$C:$C,(Raw!$B:$B=DATE(2023,11,22))*(Raw!$S:$S=$A24)*(Raw!$W:$W=C$23)))),0)

Thank you again for all your incredible help!

 

[Fluff]

Glad we could help & thanks for the feedback.