Sum if match any six numbers
 

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.

Sum if match any six numbers
 

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.

Sum if match any six numbers
 

"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.

Sum if match any six numbers
 

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.

Sum if match any six numbers
 

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.

Sum if match any six numbers
 

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.

Sum if match any six numbers
 

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