Nov 18 2020 11:00 AM
I would like to count unique values that meet multiple criteria. I assume I can use the =unique() function; however I only have access to Excel 2016. What is the best approach to create a list of the unique values in column A that match all 5 of the following criteria (APPLES, POTATOES, STRAWBERRIES, CUCUMBERS, MANGOES) in column B. Any of the unique values that match less than 5 of the criteria should not be listed. Also, the best approach to count unique values that match all 5 criteria them. Please see example.
Thank you.
Nov 18 2020 11:32 AM
Example:
How many unique distinct products did Jennifer sell in January?
Array formula in C3:
Count unique distinct values that meet multiple criteria
*Any articles, templates, third-party products or information I have provided are for reference only. While I endeavor to keep the information up to date and correct as far as I can. I make no representations or warranties, express or implied, as to the completeness, correctness, reliability, suitability, or availability of any product or information, article, file, template, or related graphic on my posts in this forum. The trust you place in such information is therefore entirely at your own risk.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Nov 18 2020 01:28 PM
Hi there,
Please follow the below written link:
https://www.get-digital-help.com/count-unique-distinct-values-that-meet-multiple-criteria-in-excel/
It should help.
Nov 18 2020 03:29 PM
For such sample
to create list of unique ID:s add to G3 formula
=IFERROR(
INDEX($B$3:$B$26,
AGGREGATE(15,6,1/
(COUNTIF($G$2:$G2,$B$3:$B$26)=0)/
COUNTIF($E$3:$E$7,$C$3:$C$26)/
(COUNTIF($B$3:$B$26,$B$3:$B$26)>=5)*
(ROW($B$3:$B$26)-ROW($B$2)),
1)
),
"")
and drag it down till empty cells appear.