Array formulas no longer calculating

**thedon_1** · 02-08-2011, 12:03 PM

I have some Array formulas in my spreadsheet which were working fine. There were sum(if...)
functions where they looked at an entry on their current sheet, found it on another sheet and added up values in another column.

When i added a new list of data to lookup on the same sheet as the array, they all just produce 0!

Please help me, why did they suddenly stop updating!

**daddylonglegs** · 02-08-2011, 12:08 PM

Are you sure there are matches? What sort of data do you have? Sometimes numeric data can be stored as text and that won't match with numbers stored as numbers.....

If that's the case try converting your text-formatted data to numbers by selecting the range (one column max) and using

Data > Text to Columns > Finish

**thedon_1** · 02-08-2011, 12:13 PM

Yep there's defenitely matches.

Both columns are text, changing them to numbers doesn't fix it.

It's weird that they work fine (checked manually) but as soon as i add or change data to check, they just all return 0.

Say my array is looking for cell A2 which contains the value 2011. It works great but if i retype 2011 in that cell, the formula will no longer calculate.

I've checked the curly brackets are on (ctrl+shift+enter) and got auto calculations on.

**DonkeyOte** · 02-08-2011, 01:15 PM

Originally Posted by thedon_1

Both columns are text, changing them to numbers doesn't fix it.

It's weird that they work fine (checked manually) but as soon as i add or change data to check, they just all return 0.

Though the cells may be format as text it's possible that the original values are numbers, when you re-enter they will be text (definitely)

To test the above run a =COUNT(A1) before & after manual alteration - is the answer 0 on both occasions ?
(obviously re: A1 use a cell that not been changed - as yet)

**thedon_1** · 02-09-2011, 05:21 AM

It seems the cells are set as general.

I tried the count thing. At first it says 0. If i paste new data or type it in, the count changes to 1.

How can i go about fixing the problem?

Thanks

**martindwilson** · 02-09-2011, 05:25 AM

post your workbook

**DonkeyOte** · 02-09-2011, 05:34 AM

Originally Posted by thedon_1

At first it says 0. If i paste new data or type it in, the count changes to 1.

OK, so this tells us the source values are text as opposed to number and as such you need to ensure you're using a text criteria at all times

A simple fix (without seeing the Array itself) would be to format the column containing those cells you're updating as Text before making changes.

Alternatively coerce both source & criteria to numbers - as outlined by dll in post #2