Count distinct entries only
The Log table has three columns of text. In A, a list of parts (widgets and flanges only). In B, parts have types. In C, they have flavors (vanilla and chocolate only).
What I want is to count the number of chocolate types of parts. Multiple instances of flange/left/chocolate only counts as one. If flange/top has both flavors, it counts as one. If flange/sideways only has vanilla, then it counts as 0.
The Metrics tab has what I want. There are 4 flange types and 6 widget types that are chocolate. However, I've had to do it indirectly. On the Parse tab, I have a list of all combinations (or is that permutations, I can never remember) of parts and types. Then I do a countifs for each of those for chocolate only on the data in the Log table. That gives me the total chocolate for each part/type.
Then on the Metrics tab, I have more countifs just counting those on the Parse tab that are greater than zero.
The problem is if a new type is added without my knowing. Say now there is a new record in the Log widget/green/chocolate. The Metrics calculation will be incorrect since that part/type isn't in the Parse table.
Can anyone think of a way to do it all directly in Metric tab and eliminate the need for the Parse tab altogether? I thought of using FREQUENCY but that requires bins to be set up. I suppose that could be done with VBA, but I'd like to see if anyone has any ideas for a formula before I go down that rabbit hole.
|