SUMPRODUCT ignore text

[sirdon]

Hi all,

I have this formula.

Please Login or Register  to view this content.

How do i get it to sum the numbers from D19:D44 but ignore any cells in column D that have text?

Many thanks

[boopathiraja]

Hi, may be this

Please Login or Register  to view this content.

[PaulM100]

Try:
=SUMPRODUCT(('2nd_XI_Test'!$B$19:B44=A2)*ISNUMBER('2nd_XI_Test'!$D$19:D44)*'2nd_XI_Test'!$D$19:D44)

[XLent]

Try:
=SUMPRODUCT(('2nd_XI_Test'!$B$19:B44=A2)*ISNUMBER('2nd_XI_Test'!$D$19:D44)*'2nd_XI_Test'!$D$19:D44)

Hi PaulM1000, the above would error for non-numeric strings given explicit coercion (*) -- splitting the arrays, per boopathiraja, resolves that issue.

if the D-range contains only text strings but where said strings are a mix of numeric & non-numeric (e.g. "apple" & "123") then something like below would work

Please Login or Register  to view this content.

however, in this instance I think it's a case of numbers & text... and, given formula, a SUMIF would suffice (whose behaviour the OP is trying to replicate)

[PaulM100]

I totaly missed the splitting part of it. I forgot that * and -- are not the same thing. Thank you for find that, so OP won't get a bad formula.

[XLent]

The * and -- have same impact in that they explicitly coerce values to numeric equivalents, e.g: "0"*1 and --"0" both return same result

The key, as you say, is splitting the arrays - separating those that need to be coerced (Booleans) from those that don't

=SUMPRODUCT(--(booleans),values)

some people use +0, or even *1, in preference to the double unary operator (--) when coercing the booleans -- all do the same thing

the unary operator is considered the fastest method but it used to be buggy when used with Volatile precedents, this is no longer the case...

and, of course, people often argue that if performance is a concern then you should not be using SUMPRODUCT to start with ;-)

[sirdon]

XLent, thank you very much. This works a treat. I appreciate everyones input. You guys are amazing.

Regards,