Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT throwing a #VALUE error when using range instead of single cell
Hi Pete,
Am Wed, 30 Dec 2015 08:53:52 -0800 (PST) schrieb : =SUMPRODUCT(1*(OR(AND(ISTEXT(Standard4[Debit]),LEN(TRIM(Standard4[Debit]))0),LEN(LEFT(Standard4[Debit],FIND(".",Standard4[Debit]&".")-1))13))) try: =SUMPRODUCT(--(LEN(TRIM(L1:L1000))0)*(L1:L1000=1000000000000)) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT throwing a #VALUE error when using range instead ofsingle cell
On Wednesday, December 30, 2015 at 9:10:33 AM UTC-8, Claus Busch wrote:
Hi Pete, Am Wed, 30 Dec 2015 08:53:52 -0800 (PST) schrieb pete: =SUMPRODUCT(1*(OR(AND(ISTEXT(Standard4[Debit]),LEN(TRIM(Standard4[Debit]))0),LEN(LEFT(Standard4[Debit],FIND(".",Standard4[Debit]&".")-1))13))) try: =SUMPRODUCT(--(LEN(TRIM(L1:L1000))0)*(L1:L1000=1000000000000)) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Sweet!!!! Thanks Claus! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula throwing up an error | Excel Programming | |||
Formula throwing up an error | Excel Programming | |||
EXCEL VBA - Find Method or Range in IE throwing error | Excel Programming | |||
aconcat UDF throwing a #NAME error | Excel Worksheet Functions | |||
Throwing an error ... on purpose! | Excel Programming |