Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > microsoft.public.excel.programming > #108399 > unrolled thread
| Started by | pegelo@gmail.com |
|---|---|
| First post | 2015-12-30 08:53 -0800 |
| Last post | 2015-12-30 09:20 -0800 |
| Articles | 3 — 3 participants |
Back to article view | Back to microsoft.public.excel.programming
SUMPRODUCT throwing a #VALUE error when using range instead of single cell pegelo@gmail.com - 2015-12-30 08:53 -0800
Re: SUMPRODUCT throwing a #VALUE error when using range instead of single cell Claus Busch <claus_busch@t-online.de> - 2015-12-30 18:10 +0100
Re: SUMPRODUCT throwing a #VALUE error when using range instead of single cell pete <pegelo@gmail.com> - 2015-12-30 09:20 -0800
| From | pegelo@gmail.com |
|---|---|
| Date | 2015-12-30 08:53 -0800 |
| Subject | SUMPRODUCT throwing a #VALUE error when using range instead of single cell |
| Message-ID | <63b67ae8-3120-4659-8de1-96303150f493@googlegroups.com> |
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
[toc] | [next] | [standalone]
| From | Claus Busch <claus_busch@t-online.de> |
|---|---|
| Date | 2015-12-30 18:10 +0100 |
| Message-ID | <n61316$ijd$1@dont-email.me> |
| In reply to | #108399 |
Hi Pete,
Am Wed, 30 Dec 2015 08:53:52 -0800 (PST) schrieb pegelo@gmail.com:
> =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
[toc] | [prev] | [next] | [standalone]
| From | pete <pegelo@gmail.com> |
|---|---|
| Date | 2015-12-30 09:20 -0800 |
| Message-ID | <d34bdeb0-3f41-43ae-8fe3-de822eac3e59@googlegroups.com> |
| In reply to | #108400 |
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!
[toc] | [prev] | [standalone]
Back to top | Article view | microsoft.public.excel.programming
csiph-web