Greetings!
I can't really think of a good description for the title. I tried all manner of search terms and came up with some good stuff, but I'm having a really tough time describing what I need without showing it, so here goes:
Sample.png
(Would have done a Table entry, but it looked way taller than it needed to be.)
What I need is a formula that can look at a range of values, look those values up in the "Tags" column, see if they all belong to one "Object", and then checks for Passed and/or Failed (depending on what is desired) in the Result column, and then, finally, returns the number of hits in the Result column. For instance, if my range had "Black" in one cell and "Critter" in another, with no specification for Passed or Failed, it would return 2. "Smooth" + "Black" + Passed would return 1, "Cylindrical" + Failed would return 1, no tags + Passed would return 3, etc.
Originally, on the chart that uses this table, I just used a filter for comparing the Passed/Failed results based on Tags, but that just compares two separate tags and doesn't refine the results. Obvious now, took me an embarrassingly long time to realize that.
I tried doing all kinds of stuff with a Pivot Table and couldn't get it to do what I needed here, either.
I imagine what I need here is some manner of SUMPRODUCT magic. I feel like I'm on the verge of getting it, and I'm pretty good at coming up with spiffy SUMPRODUCT formulas, but this one evades me after trying so, so many things. I have a feeling that I'm overthinking it. A large number of issues that stump me are because I bury the simple answer in my head under REALLY NEATO FORMULAS! that don't do what I need.
Generally I need to stay out of VBA and work exclusively in formulas, but VBA is a valid option here. Even though, I would still really like to see the formula. I loves me a clever formula.
I need to stay pretty rigid as to how the data is structured. I can't put Passed or Failed into the Tags column, for instance.
I think that covers what I need. Sorry for the hugeness. Any ideas?
Bookmarks