Can somebody tell me why the following formula doesn't work?
=SUMPRODUCT(--(OFFSET(Range1, ROW()-ROW(Range1), 0, 1, 32)=Range1), --(Range1""))
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
"Range1" is a named range that is 1r x 32 columns.
The intent of the formula is to count the number of
entries in the particular row that match the correct entries that are listed in Range1. If no value has been entered in a
cell in Range1, then I don't want that counted as a match.
It works if the Offset(...) is replaced with the
actual range that the offset should refer to. However, I want the formula to be more general so that I can copy it.
EDIT: I think I've narrowed it down to the ROW() part of the formula. I can't get it to work
in any way when that is in the formula.