SUMPRODUCT throwing a #VALUE error when using range instead of single cell
Hi, I have a formula that uses SUMPRODUCT to tally the occurrences of invalid data entered into a column in an Excel Table.
=SUMPRODUCT(1*(OR(AND(ISTEXT($L17),LEN(TRIM($L17)) 0),LEN(LEFT($L17,FIND(".",$L17&".")-1))13)))
The formula is intended to count cells which contain data that is either text (but not just spaces) or is a number and is longer than 13 digits to the left of the decimal point.
The formula above works for a single cell, but when i put in a range (the column in the Excel Table) for the cell address I get a #VALUE error
=SUMPRODUCT(1*(OR(AND(ISTEXT(Standard4[Debit]),LEN(TRIM(Standard4[Debit]))0),LEN(LEFT(Standard4[Debit],FIND(".",Standard4[Debit]&".")-1))13)))
Any ideas?
thanks!
pete
|