Unique with Filter : Formula issue

[chandrashekar]

Hello,

am trying below formula to get unique count based on multi criteria but am getting wrong count.

Can you please let me know where is error.

=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!M2:M59039,(Sheet1!B2:B59039<>"HD")*(Sheet1!AH2:AH59039=1)*(Sheet1!AM2:AM59039="CH")*IFERROR(Sheet1!AL2:AL59039="First",0)+IFERROR(Sheet1!AL2:AL59039="Second",0)+IFERROR(Sheet1!AL2:AL59039="Three",0)))),0)

Regards,
Chandrashekar B

 

[Peter_SSs]

What about a small set of sample dummy data (say about 20 rows) with XL2BB and tell us
- what the expected result is
- why that is the expected result.

With XL2BB, hide all the irrelevant columns before making your Mini Sheet

 

[chandrashekar]

Hello,

am trying below formula to get unique count based on multi criteria but am getting wrong count.

Can you please let me know where is error.
=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!M2:M59039,(Sheet1!B2:B59039<>"HD")*(Sheet1!AH2:AH59039=1)*(Sheet1!AM2:AM59039="CH")*IFERROR(Sheet1!AL2:AL59039="First",0)+IFERROR(Sheet1!AL2:AL59039="Second",0)+IFERROR(Sheet1!AL2:AL59039="Three",0)))),0)

Issue with below statement where am trying to add multi condition as below
IFERROR(Sheet1!AL2:AL59039="First",0)+IFERROR(Sheet1!AL2:AL59039="Second",0)+IFERROR(Sheet1!AL2:AL59039="Three",0)

However it is working with first condition as below
=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!M2:M59039,(Sheet1!B2:B59039<>"HD")*(Sheet1!AH2:AH59039=1)*(Sheet1!AM2:AM59039="CH")*IFERROR(Sheet1!AL2:AL59039="First",0)))),0)

Regards,
Chandrashekar B

 

[chandrashekar]

What about a small set of sample dummy data (say about 20 rows) with XL2BB and tell us
- what the expected result is
- why that is the expected result.

With XL2BB, hide all the irrelevant columns before making your Mini Sheet

Hello Peter,

Cannot share data as it is confidential but am trying explain in detail.

Hello,

am trying below formula to get unique count based on multi criteria but am getting wrong count.

Can you please let me know where is error.
=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!M2:M59039,(Sheet1!B2:B59039<>"HD")*(Sheet1!AH2:AH59039=1)*(Sheet1!AM2:AM59039="CH")*IFERROR(Sheet1!AL2:AL59039="First",0)+IFERROR(Sheet1!AL2:AL59039="Second",0)+IFERROR(Sheet1!AL2:AL59039="Three",0)))),0)

Issue with below statement where am trying to add multi condition as below
IFERROR(Sheet1!AL2:AL59039="First",0)+IFERROR(Sheet1!AL2:AL59039="Second",0)+IFERROR(Sheet1!AL2:AL59039="Three",0)

However it is working with first condition as below
=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!M2:M59039,(Sheet1!B2:B59039<>"HD")*(Sheet1!AH2:AH59039=1)*(Sheet1!AM2:AM59039="CH")*IFERROR(Sheet1!AL2:AL59039="First",0)))),0)

Regards,
Chandrashekar B

 

[Peter_SSs]

Cannot share data as it is confidential

That is why I said ..

What about a small set of sample dummy data

It is very difficult to know why you are not getting the results that you expect without sample data. That is particularly so when it seems to me to be working.

Here is my dummy sample data. I have marked green all the rows that meet the basic criteria and I have marked blue the unique values in those green rows.

chandrashekar.xlsm
	B											M																					AH				AL	AM
1	H2											H13																					H34				H38	H39
2	HD											a																					1				First	CH
3	PQ											b																					1				First	CH
4	HD											c																					1				First	CH
5	PQ											d																					1				First	CH
6	PQ											s																					1				First	CH
7	FF											a																					1				First	CH
8	HD											s																					1				First	CH
9	GG											d																					1				First	CH
10	HD											c																					1				First	CH
11	GG											b																					1				First	CH
12	AB											d																					5				First	CH
13	AB											s																					2				First	CH
14	AA											x																					1				Second	CH
15	AA											y																					1				Three	CH
16	AA											s																					1				First	XX
17	AA											d																					2				First	XX
18	AA											c																					2				First	XX
19	AA											v																					2				First	XX
20	AA											f																					1				First	XX
Sheet1

Here is your formula from post 1. It seems to me to be giving the correct result.

chandrashekar.xlsm
	A
1	6
Sheet2

Cell Formulas
Range	Formula
A1	A1	=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!M2:M59039,(Sheet1!B2:B59039<>"HD")*(Sheet1!AH2:AH59039=1)*(Sheet1!AM2:AM59039="CH")*IFERROR(Sheet1!AL2:AL59039="First",0)+IFERROR(Sheet1!AL2:AL59039="Second",0)+IFERROR(Sheet1!AL2:AL59039="Three",0)))),0)

So, if that is correct for me but not for you is is hard to determine what the issue is for you without seeing something else.

 

[chandrashekar]

That is why I said ..


It is very difficult to know why you are not getting the results that you expect without sample data. That is particularly so when it seems to me to be working.

Here is my dummy sample data. I have marked green all the rows that meet the basic criteria and I have marked blue the unique values in those green rows.

chandrashekar.xlsm
	B											M																					AH				AL	AM
1	H2											H13																					H34				H38	H39
2	HD											a																					1				First	CH
3	PQ											b																					1				First	CH
4	HD											c																					1				First	CH
5	PQ											d																					1				First	CH
6	PQ											s																					1				First	CH
7	FF											a																					1				First	CH
8	HD											s																					1				First	CH
9	GG											d																					1				First	CH
10	HD											c																					1				First	CH
11	GG											b																					1				First	CH
12	AB											d																					5				First	CH
13	AB											s																					2				First	CH
14	AA											x																					1				Second	CH
15	AA											y																					1				Three	CH
16	AA											s																					1				First	XX
17	AA											d																					2				First	XX
18	AA											c																					2				First	XX
19	AA											v																					2				First	XX
20	AA											f																					1				First	XX
Sheet1

Here is your formula from post 1. It seems to me to be giving the correct result.

chandrashekar.xlsm
	A
1	6
Sheet2

Cell Formulas
Range	Formula
A1	A1	=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!M2:M59039,(Sheet1!B2:B59039<>"HD")*(Sheet1!AH2:AH59039=1)*(Sheet1!AM2:AM59039="CH")*IFERROR(Sheet1!AL2:AL59039="First",0)+IFERROR(Sheet1!AL2:AL59039="Second",0)+IFERROR(Sheet1!AL2:AL59039="Three",0)))),0)

So, if that is correct for me but not for you is is hard to determine what the issue is for you without seeing something else.

Hello Peter,

Issue with adding multi condition however it is working fine with single condition. I have added sample data and not getting expected result.

Sample data: Sample data

 

[Peter_SSs]

Thanks for the sample data. But shouldn't the answer be 10, as marked blue? (I've marked green all cells that meet the criteria this time)

Book8.xlsx
	A	B	C	D	E
1	Column1	Column2	Column3	Column4	Column5
2	IE	CT1	1	Second	CH
3	KESS	CT2	1	Second	SH
4	KESS	CT3	1	Second	CH
5	KESS	CT4	1	Second	CH
6	Mylife	CT5	1	First	CH
7	Mylife	CT6	1	First	SH
8	Mylife	CT7	1	First	CH
9	Mylife	CH1	1	First	CH
10	KESS	CT8	1	Second	CH
11	Mylife	CH2	1	First	CH
12	Mylife	CH2	1	First	CH
13	Mylife	CH2	1	First	CH
14	Mylife	CH3	1	First	CH
15	Mylife	CH3	1	First	CH
16	Mylife	CH3	1	First	CH
17	Mylife	CH4	1	First	CH
18	Mylife	CH4	1	First	CH
19	Mylife	CT9	1	#N/A	CH
20	KESS	CH5	1	Second	SH
Sheet1

I believe what is missing (& was from the original too) is parentheses around the 'multiple conditions' section

=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!B2:B20,(Sheet1!A2:A20<>"HD")*(Sheet1!C2:C20=1)*(Sheet1!E2:E20="CH")*(IFERROR(Sheet1!D2:D20="First",0)+IFERROR(Sheet1!D2:D20="Second",0))))),0)

 

[chandrashekar]

=IFERROR(ROWS(UNIQUE(FILTER(Sheet1!B2:B20,(Sheet1!A2:A20<>"HD")*(Sheet1!C2:C20=1)*(Sheet1!E2:E20="CH")*(IFERROR(Sheet1!D2:D20="First",0)+IFERROR(Sheet1!D2:D20="Second",0))))),0)

Hello Peter,
Thanks a lot now it is working fine.

Regards,
Chandrashekar B

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.