Hi,

I need an array function such that:

Sum(If( G2:G5836 = <any six numeric digits, I2:I5836,0))

Obviously, I need the part on how to match any six numeric digits (e.g.
123456) in the above formula, can someone please help me out on this?

Thanks much in advance.

Try:

=SUM(IF(ISNUMBER(G2:G5836)*LEN(G2:G5836)=6, I2:I5836,0))

Array entered with Ctrl + Shift + Enter or:

=SUMPRODUCT((ISNUMBER(G2:G5836)*(LEN(G2:G5836)=6)* I2:I5836))

Normally entered


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"XP" wrote in message
...
Hi,

I need an array function such that:

Sum(If( G2:G5836 = <any six numeric digits, I2:I5836,0))

Obviously, I need the part on how to match any six numeric digits (e.g.
123456) in the above formula, can someone please help me out on this?

Thanks much in advance.

"Sandy Mann" wrote...
Try:
....
=SUMPRODUCT((ISNUMBER(G2:G5836)*(LEN(G2:G5836)=6) *I2:I5836))
....

Would include -87654, 12.345, etc. Try instead

=SUMPRODUCT((G2:G5836=100000)*(G2:G5836<=999999)
*(G2:G5836=INT(G2:G5836)),I2:I5836)

Rule of thumb: NEVER use text operations on numeric data if there's an
equivalent arithmetic operation.

Would include -87654, 12.345, etc
Very true. That's why I frequent these NG's - to try to keep the Alzheimer's
at bay.

Thank you for pointing it out.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Harlan Grove" wrote in message
...
"Sandy Mann" wrote...
Try:
...
=SUMPRODUCT((ISNUMBER(G2:G5836)*(LEN(G2:G5836)=6 )*I2:I5836))
...

Would include -87654, 12.345, etc. Try instead

=SUMPRODUCT((G2:G5836=100000)*(G2:G5836<=999999)
*(G2:G5836=INT(G2:G5836)),I2:I5836)

Rule of thumb: NEVER use text operations on numeric data if there's an
equivalent arithmetic operation.

Try this:
=SUMPRODUCT(--(A2:A10=123),--(B2:B10=456),--(C2:C10=789),--(D2:D10=1011),--(E2:E10=1213),--(F2:F10=1415),G2:G10)

I think you can have up to 30 columns.

See this for more info.:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards,
Ryan---
--
RyGuy


"XP" wrote:

Hi,

I need an array function such that:

Sum(If( G2:G5836 = <any six numeric digits, I2:I5836,0))

Obviously, I need the part on how to match any six numeric digits (e.g.
123456) in the above formula, can someone please help me out on this?

Thanks much in advance.

Well, you can still spell it, Sandy, so it must be working for you.
<bg

Pete

On Jun 23, 10:32*pm, "Sandy Mann" wrote:
That's why I frequent these NG's - to try to keep the Alzheimer's at bay.

Thank you for pointing it out.

Hi,
Perhaps this:
=IF(SUMPRODUCT(--(LEN(G2:G5836)=6)*(ISNUMBER(G2:G5836)))=1,I2:I5836 ,0)
Array entered.
Regards - Dave.