Nov 22 2021 12:46 PM
I've found many examples that come really close to what I need, but not quite right... I need to count how many unique locations contain a part after doing multiple filters for the specific part. I've tried many combinations of count, if, sum, unique, filter, etc. Can't quite get there.
Assume I do a database dump on the first day of the month showing all parts in all locations. One of the summary points for my monthly report is to drill down to a very specific part, in a specific state, and count how many locations are being used (consumed!) by that part.
Here is a really simplified example:
I want a count, a single integer number, of unique locations are being used by light red parts in a state of "return". (If my data were this neat, I could do a pivot table with appropriate filters, but it isn't. I need to use some LEFT or IF functions to pull out key text to get the correct matches. I've got a)
The correct answer for "how many locations are being consumed by light red parts in a state of return" is two. Two locations are being used by those returned goods.
I'm running with a 365 subscription. I think COUNTA, UNIQUE and two or three FILTERS should get my number, but so far, no luck!
Nov 22 2021 12:58 PM
Enter red in G2, light in H2 and return in I2.
Formula in J2:
=SUM(--(LEN(UNIQUE(FILTER(Table1[location],(Table1[part]=G2)*(Table1[variety]=H2)*(Table1[state]=I2),"")))>0))
Source:Count unique values with criteria
See the attached version.
It would also be possible to use a pivot table for this (you have to add the source range to the Data Model)
Nov 22 2021 01:16 PM
Nov 22 2021 02:05 PM
SolutionNov 22 2021 02:05 PM
Solution