Summing up an array with non numeric values

[Subtle]

Hi all.

Can anyone help me please.

I am trying to sum all the numeric values in the array in cell B13 but I keep getting this error.

The offset function produces this array: {67;53;"Data";"Data";81;"Data";70;"Data";"Data";"Data"}

Is there a way to sum only the numbers within this array? Either by using OFFSET function or any other function?

Thanks.

Edit: Apologies for not being very clear. I want the formula to sum the values in the array B2:B11 based on its index in A2:A11.
so the indexes are {4, 3, 0, 0, 8, 0, 5, 0, 0, 0,}. But as "0" is not an index value, it would lead to an error. So the required formula should ignore "0" values, and only count non zero values.
So the final result in B13 should be "271" based on {67 + 53 + 81 + 70}. These values have indexes {4, 3, 8, 5}.

[63falcondude]

What, specifically in your sample, should the working formula do?

What would the desired result of that working formula be?

[Glenn Kennedy]

What are you wanting to do (in words...) A non-working formula does not help!! Your explanation didn't help much, either, as there are 3 columns of numbers with no interspersed text!!

[CK76]

May be...

Please Login or Register  to view this content.

Confirmed as array (CTRL + SHIFT + ENTER)

EDIT: I'm guessing that OP wanted to sum Data column based on # listed in Index column (i.e. Row# 4 = 67, 3 = 53, 0 = null etc).

[63falcondude]

I see the OP edited post #1.

CK's formula should work. You can also use this:

=SUMPRODUCT(COUNTIF(A2:A11,B2:B11),C2:C11)

Side note: I recommend putting at least one blank column between A and B since they are not related like columns B and C are.
This alone created some confusion (at least for me).

[Subtle]

@ck

Genious solution. This works. Well done and thank you very much.

[63falcondude]

Glad we could help.

[CK76]

You are welcome