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
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.
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))
As noted this should be confirmed with CtrlShiftEnter or you could use Sumproduct rather than Sum.
- Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?
- Dynamically Inserting a row and iterating over it in VBA?
- VBA class member function and class definition problem continues
- How can I perform COALESCE in power query?
- VBA class member function and class definition problem
- How can I set OptionButton font using VBA For Loop?
- VBA remove string custom salutation name from list table excel
- How to import many dataframes from R into a one Excel file in a simple way?
- How to add a missing column to the Transform Sample File
- Pull data from multiple workbooks based on a dynamic list
- Dependent dropdown list in Excel ,Data Validation and VBA
- Excel Formula: Filter Group based on Smallest Values
- Find column name of active cell within ListObject in Excel
- Strange characters found in XML file and PowerShell output after exporting from Excel: ​
- MID function inside COUNTIFS
- Finding Max of 3 Inputs VBA
- How to align duplicates on the same rows in Excel
- How to use .HTMLBody to hyperlink a cell value?
- Adding cell values, specific to each page, to each header
- Excel VBA macro for URL redirect detecting returns 200 instead of 301 sometimes
- Count with specific values in columns
- Convert IEEE Float hex string to an Excel VBA Single (float)
- Openpyxl or Pandas, which is better at reading data from a excel file and returning corresponding values
- Excel Function: Using a specific criteria to form an array to corresponding values
- How to pass metadata to Google Drive API upload with VBA
- IsError not capturing error returned by expression
- Excel VBA multi select drop down list
- Imported Excel file with dates, came out as numbers, trying to return to date format
- Creating Conditional Column based on another Table's Column
- Convert Range to a table