% Match on Text ??? Need formula help!

[simfonik]

This is what I am trying to do with a single formula:

I have values of across D6:G6 that I want to compare to the value
across D7:G7. I'm trying to come up with a formula that will return
% match....

example:

D6=Neutral vs. D7=Neutral
E6=Negative vs. E7=Good
F6=Neutral vs. F7=Neutral
G6=Good vs. G7=Good

With the data set above, the formula should return a value of 75%
since 3 out of 4 matched.

Any ideas?

Thanks!!
S

 

[Dave Peterson]

This'll work if row 7 always has entries:

=SUMPRODUCT(--(D6:G6=D7:G7))/COUNTA(D7:G7)
(format as a percent)

Or maybe if cells d6:g7 could be empty:
=SUMPRODUCT(--(D6:G6=D7:G7))/SUMPRODUCT(--(D6:G6&D77<>""))