Sum if match any six numbers

[XP]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Harlan Grove]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[ryguy7272]

Try this
=SUMPRODUCT(--(A2:A10=123),--(B2:B10=456),--(C2:C10=789),--(D210=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---

 

[Pete_UK]

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

Pete

 

[Dave]

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