SOLVED

counting unique values after multiple filters

Brass Contributor

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:

Brian_Paasch_0-1637613196314.png

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!

3 Replies

@Brian_Paasch 

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)

Thanks! This looks really helpful. Still struggling a bit using wild cards. Say if I needed to use "retu*" in place of your using cell I2 containing the word "return". That seems to break something. But I'm pretty sure I'll get around that. Thanks again, this was hugely helpful!
best response confirmed by Brian_Paasch (Brass Contributor)
Solution
Got it! I have multiple variations of the "return" state. BUT, they all start with the word "return". So I added a LEFT to that last step, i.e. LEFT(Table1[state],6)="return"), and that did the trick! Thanks again!
1 best response

Accepted Solutions
best response confirmed by Brian_Paasch (Brass Contributor)
Solution
Got it! I have multiple variations of the "return" state. BUT, they all start with the word "return". So I added a LEFT to that last step, i.e. LEFT(Table1[state],6)="return"), and that did the trick! Thanks again!

View solution in original post