Trying to count unique items from a data set with multiple conditions

Question

Hi,&nbsp;I just joined this community and looking for some help! I have a set of data from which I am trying to count unique items under certain categories. For example, there are multiple categories and under each of these categories, there are different items. I am trying to count how many unique items are under each category. I tried to combine COUNTIF and UNIQUE functions, but unfortunately not getting the result. What should be the best way to get a correct count of unique items under each category? Any help will be hugely appreciated!&nbsp;Here is an example of the data set I have:&nbsp;VendorProductDescriptionRef/serial NbrReasonRcvd DateCategoryAQF1Q44PRODUCT Q TW11831010/19/2021ACCAQF1Q44PRODUCT Q TW11841010/19/2021ACCAQF1Q22PRODUCT Q - FL11851010/19/2021ACCAQF1Q22PRODUCT Q - FL11861010/19/2021ACCAQF1Q22PRODUCT Q - FL11871010/19/2021ACCAQF1Q22PRODUCT Q - FL12621012/23/2022ACCAQF1Q23PRODUCT Q - QN12631012/23/2022BBBAQF1Q23PRODUCT Q - QN56811010/12/2022BBBAQF1Q23PRODUCT Q - QN11391303/03/2013BBBAQF1Q23PRODUCT Q - QN43441302/24/2016BBBAQF1Q23PRODUCT Q - QN43451302/24/2016BBBAQF2Q37PRODUCT Q - KG52681303/16/2023DDDAQF2Q37PRODUCT Q - KG23341306/27/2013DDDAQF2Q37PRODUCT Q - KG23381306/27/2013DDDAQF2Q37PRODUCT Q - KG23391306/27/2013DDDAQF2Q37PRODUCT Q - KG23401306/27/2013DDDAQF2Q37PRODUCT Q - KG23411306/27/2013DDDAQF1B03PRODUCT AQ - FL23432606/27/2013EEEAQF1B03PRODUCT AQ - FL23502607/02/2013EEEAQF1B03PRODUCT AQ - FL23512607/02/2013EEEAQF1B03PRODUCT AQ - FL23522607/02/2013EEEAQF1B03PRODUCT AQ - FL23532607/02/2013EEEAQF1B03PRODUCT AQ - FL23542607/02/2013EEEAQF2F04PRODUCT AQ - TW23553707/02/2013AAAAQF2F04PRODUCT AQ - TW23563707/02/2013AAAAQF2F04PRODUCT AQ - TW23573707/02/2013AAAAQF2F04PRODUCT AQ - TW23583707/02/2013AAAAQF2F04PRODUCT AQ - TW23593707/02/2013AAAAQF2F04PRODUCT AQ - TW23603707/02/2013AAAAQF2F04PRODUCT AQ - TW23613707/02/2013AAAAQF2C22PRODUCT AQ - KG23922607/11/2013BBBAQF2C22PRODUCT AQ - KG23932607/11/2013BBBAQF2C22PRODUCT AQ - KG23942607/11/2013BBBAQF2C22PRODUCT AQ - KG23992607/11/2013BBBAQF2C22PRODUCT AQ - KG24012607/11/2013BBBAQF2C22PRODUCT AQ - KG24022607/11/2013BBBABC1T22PRODUCT T - KG24035007/11/2013ACCABC1T22PRODUCT T - KG24045007/11/2013ACCABC1T22PRODUCT T - KG24055007/11/2013ACCABC1T22PRODUCT T - KG24065007/11/2013ACCABC2T32PRODUCT T - QN24095507/11/2013ACCABC2T32PRODUCT T - QN24105507/11/2013ACCABC2T32PRODUCT T - QN24115507/11/2013ACCABC2T32PRODUCT T - QN24125507/11/2013ACCDEF1A03PRODUCT A - QN918185011/27/2023DDDDEF1A03PRODUCT A - QN918195011/27/2023DDDDEF1A03PRODUCT A - QN918205011/27/2023DDDDEF1A03PRODUCT A - QN918215011/27/2023DDDDEF4A04PRODUCT A - FL918226011/27/2023EEEDEF4A04PRODUCT A - FL918236011/27/2023EEEDEF4A04PRODUCT A - FL918246011/27/2023EEEDEF4A04PRODUCT A - FL918256011/27/2023EEEDEF4A04PRODUCT A - FL918266011/27/2023EEEDEF4A04PRODUCT A - FL918276011/27/2023EEEGHI1R01PRODUCT R - TW918287711/27/2023ACCGHI1R01PRODUCT R - TW918297711/27/2023ACCGHI1R01PRODUCT R - TW918307711/27/2023ACCGHI1R01PRODUCT R - TW918317711/27/2023ACCGHI1R01PRODUCT R - TW918327711/27/2023ACCGHI3S03PRODUCT S - FL959051412/30/2023BBBGHI3S03PRODUCT S - FL959061412/30/2023BBBGHI3S03PRODUCT S - FL959071412/30/2023BBBGHI3S03PRODUCT S - FL959081412/30/2023BBBGHI3S03PRODUCT S - FL959091412/30/2023BBBGHI3S03PRODUCT S - FL959101412/30/2023BBBGHI3S03PRODUCT S - FL959111412/30/2023BBB&nbsp; &nbsp;&nbsp;

hansvogelaar · Answer

Anisul_Haque&nbsp;
You can create a pivot table from the data.
In the 'PivotTable from table or range' dialog, tick the check box 'Add this data to the Data Model'.

Add Category to the Rows area and Product to the Values area.
Then click the drop-down in the Count of Product field in the Values area and change the summary function to Distinct Count.

Result:

hansvogelaar · Answer

Anisul_Haque&nbsp;
In the attached workbook, I have also included a formula solution.

anisul_haque · Answer

Hi Hans Vogelaar,Thanks for the quick reply, really appreciate it. I will definitely try this, can there be a formula or function to extract the same information, just curious!Anisul Haque

anisul_haque · Answer

Thank you so much&nbsp;HansVogelaar&nbsp;

patrick2788 · Answer

Anisul_Haque&nbsp;With a formula and the data being tabled:=LET(
    category, SORT(UNIQUE(Table1[Category])),
    UniqueProducts, LAMBDA(acc, v,
        LET(
            filtered, FILTER(Table1[Product], Table1[Category] = v),
            distinct, ROWS(UNIQUE(filtered)),
            VSTACK(acc, HSTACK(v, distinct))
        )
    ),
    REDUCE({"Category", "Count"}, category, UniqueProducts)
)

Forum Discussion

Trying to count unique items from a data set with multiple conditions

7 Replies

Resources

Vendor	Product	Description	Ref/serial Nbr	Reason	Rcvd Date	Category
AQF	1Q44	PRODUCT Q TW	1183	10	10/19/2021	ACC
AQF	1Q44	PRODUCT Q TW	1184	10	10/19/2021	ACC
AQF	1Q22	PRODUCT Q - FL	1185	10	10/19/2021	ACC
AQF	1Q22	PRODUCT Q - FL	1186	10	10/19/2021	ACC
AQF	1Q22	PRODUCT Q - FL	1187	10	10/19/2021	ACC
AQF	1Q22	PRODUCT Q - FL	1262	10	12/23/2022	ACC
AQF	1Q23	PRODUCT Q - QN	1263	10	12/23/2022	BBB
AQF	1Q23	PRODUCT Q - QN	5681	10	10/12/2022	BBB
AQF	1Q23	PRODUCT Q - QN	1139	13	03/03/2013	BBB
AQF	1Q23	PRODUCT Q - QN	4344	13	02/24/2016	BBB
AQF	1Q23	PRODUCT Q - QN	4345	13	02/24/2016	BBB
AQF	2Q37	PRODUCT Q - KG	5268	13	03/16/2023	DDD
AQF	2Q37	PRODUCT Q - KG	2334	13	06/27/2013	DDD
AQF	2Q37	PRODUCT Q - KG	2338	13	06/27/2013	DDD
AQF	2Q37	PRODUCT Q - KG	2339	13	06/27/2013	DDD
AQF	2Q37	PRODUCT Q - KG	2340	13	06/27/2013	DDD
AQF	2Q37	PRODUCT Q - KG	2341	13	06/27/2013	DDD
AQF	1B03	PRODUCT AQ - FL	2343	26	06/27/2013	EEE
AQF	1B03	PRODUCT AQ - FL	2350	26	07/02/2013	EEE
AQF	1B03	PRODUCT AQ - FL	2351	26	07/02/2013	EEE
AQF	1B03	PRODUCT AQ - FL	2352	26	07/02/2013	EEE
AQF	1B03	PRODUCT AQ - FL	2353	26	07/02/2013	EEE
AQF	1B03	PRODUCT AQ - FL	2354	26	07/02/2013	EEE
AQF	2F04	PRODUCT AQ - TW	2355	37	07/02/2013	AAA
AQF	2F04	PRODUCT AQ - TW	2356	37	07/02/2013	AAA
AQF	2F04	PRODUCT AQ - TW	2357	37	07/02/2013	AAA
AQF	2F04	PRODUCT AQ - TW	2358	37	07/02/2013	AAA
AQF	2F04	PRODUCT AQ - TW	2359	37	07/02/2013	AAA
AQF	2F04	PRODUCT AQ - TW	2360	37	07/02/2013	AAA
AQF	2F04	PRODUCT AQ - TW	2361	37	07/02/2013	AAA
AQF	2C22	PRODUCT AQ - KG	2392	26	07/11/2013	BBB
AQF	2C22	PRODUCT AQ - KG	2393	26	07/11/2013	BBB
AQF	2C22	PRODUCT AQ - KG	2394	26	07/11/2013	BBB
AQF	2C22	PRODUCT AQ - KG	2399	26	07/11/2013	BBB
AQF	2C22	PRODUCT AQ - KG	2401	26	07/11/2013	BBB
AQF	2C22	PRODUCT AQ - KG	2402	26	07/11/2013	BBB
ABC	1T22	PRODUCT T - KG	2403	50	07/11/2013	ACC
ABC	1T22	PRODUCT T - KG	2404	50	07/11/2013	ACC
ABC	1T22	PRODUCT T - KG	2405	50	07/11/2013	ACC
ABC	1T22	PRODUCT T - KG	2406	50	07/11/2013	ACC
ABC	2T32	PRODUCT T - QN	2409	55	07/11/2013	ACC
ABC	2T32	PRODUCT T - QN	2410	55	07/11/2013	ACC
ABC	2T32	PRODUCT T - QN	2411	55	07/11/2013	ACC
ABC	2T32	PRODUCT T - QN	2412	55	07/11/2013	ACC
DEF	1A03	PRODUCT A - QN	91818	50	11/27/2023	DDD
DEF	1A03	PRODUCT A - QN	91819	50	11/27/2023	DDD
DEF	1A03	PRODUCT A - QN	91820	50	11/27/2023	DDD
DEF	1A03	PRODUCT A - QN	91821	50	11/27/2023	DDD
DEF	4A04	PRODUCT A - FL	91822	60	11/27/2023	EEE
DEF	4A04	PRODUCT A - FL	91823	60	11/27/2023	EEE
DEF	4A04	PRODUCT A - FL	91824	60	11/27/2023	EEE
DEF	4A04	PRODUCT A - FL	91825	60	11/27/2023	EEE
DEF	4A04	PRODUCT A - FL	91826	60	11/27/2023	EEE
DEF	4A04	PRODUCT A - FL	91827	60	11/27/2023	EEE
GHI	1R01	PRODUCT R - TW	91828	77	11/27/2023	ACC
GHI	1R01	PRODUCT R - TW	91829	77	11/27/2023	ACC
GHI	1R01	PRODUCT R - TW	91830	77	11/27/2023	ACC
GHI	1R01	PRODUCT R - TW	91831	77	11/27/2023	ACC
GHI	1R01	PRODUCT R - TW	91832	77	11/27/2023	ACC
GHI	3S03	PRODUCT S - FL	95905	14	12/30/2023	BBB
GHI	3S03	PRODUCT S - FL	95906	14	12/30/2023	BBB
GHI	3S03	PRODUCT S - FL	95907	14	12/30/2023	BBB
GHI	3S03	PRODUCT S - FL	95908	14	12/30/2023	BBB
GHI	3S03	PRODUCT S - FL	95909	14	12/30/2023	BBB
GHI	3S03	PRODUCT S - FL	95910	14	12/30/2023	BBB
GHI	3S03	PRODUCT S - FL	95911	14	12/30/2023	BBB