CODEWITHSUNDEEP
excelmatchuniquecountifwhitelist
KananBendu
KananBendu

Reputation: 3

Counting number of unique values in one column if data in a 2nd column matches anything in a 3rd column

I have data that will be changing daily. I'd simply like to get a count of unique values from the first column if the data in the same row of the 2nd column matches anything in the 3rd column located elsewhere. For the most part the 3rd column to the side will stay the same and will be like an external whitelist and the first 2 columns will change daily. I'd like to do this with a formula and not using the drop down filters

Example

For example, there are 3 possible locations in this. I want a count of how many locations (1, 2, or 3) that the desired colored product is found in.

Home contains both red and pink from the desired list. Car contains only red from the desired list. Even though there are 8 lines with the correct color or multiple colors for each location, I only want to know how many different locations contain those colors. In this case the answer should come back as 2.

I've tried multiple combinations of countif/index/match/etc but I have not figured out the proper way to do it. Also this will be used in Excel 2016.

Upvotes: -3

Views: 117

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34370

For Excel 2016 you would be looking at this method of doing unique counts. In your case you could use Countif to check that a colour appears in the list and then (since the locations are strings) use match to convert them into numbers as shown in the second example in the reference.

=SUM(--(FREQUENCY(IF(COUNTIF(Table2[Color],Table1[Products]),MATCH(Table1[Locations],Table1[Locations],0)),MATCH(Table1[Locations],Table1[Locations],0))>0))

enter image description here

As noted this should be confirmed with CtrlShiftEnter or you could use Sumproduct rather than Sum.

Upvotes: 1

Denton Thomas
Denton Thomas

Reputation: 353

Since you're on 2016, I suggest you break down your process into separate columns. That should make this both maintainable and clear.

  1. Add a column to your Data that seeks your Product flag in the list of Desired values. That will let you quickly label the match status for each row (below: MatchFlag column)
  2. Add a column to your Desired flags table. Look up all the matches in the same ay (below: MatchData column)
  3. Count the MatchData column

I've put your data into two Excel Tables: Flags and Data. That saves you from filling an unpredictable number of rows with your formulas.

Flags MatchData Labels Values MatchFlag CountFlagMatches
red TRUE home red red 2
pink TRUE home red red
yellow home blue
green home blue
home pink pink
home pink pink
work blue
work blue
car blue
car blue
car red red
car red red

MatchData: =IFNA(IF(VLOOKUP([@Flags], Data[MatchFlag], 1, FALSE)<>"", TRUE, FALSE), "")

MatchFlags: =IFNA(VLOOKUP([@Values], Flags[Flags], 1, FALSE), "")

CountFlagMatches: =COUNTIF(Flags[MatchData], "=TRUE")

Upvotes: 0

Related Questions