On Wed, 22 Jul 2009 13:07:11 GMT, Lars-Åke Aspelin
wrote:

On Wed, 22 Jul 2009 06:00:01 -0700, James
wrote:

Hi Luke

Doesn't matter which result it returns as it'll always be the same e.g. in
your example both rows would just show 22 rather than 22 and 25 as you have
done. ZZ will never appear in two columns on the same row only once.

P.S. Thanks for your advice on the UDF, bit beyond my knowledge but I'll
give it a go over the next few days ;)


This formula will take care of the possibility that "ZZ" is found on
more than one row.

=SUMPRODUCT(--((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ")0)*(D1: D10))/SUMPRODUCT(--((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ")0))

Hope this helps / Lars-Åke


And if you know that ZZ is never found on more than one column on any
row, the formula can be shortened a bit, like this:

=SUMPRODUCT(((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10=" ZZ"))*(D1:D10))/SUMPRODUCT((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ "))

Lars-Åke