sum not working for range

[xxentric]

=SUM(IF('C:\Documents and Settings\chipfab\Desktop\[test.xls]Sheet1'!$A$1:$A$9="12-1",1,0))

why will this only work if the value is in A1 ??? if its in A5 it returns 0 or false

 

[combo]

You need to array-enter this formula (CTRL+SHIFT+ENTER).

combo

 

[xxentric]

sweet that does work.. how come it wont stay? ex. if i then delete the value its looking for and reenter it, it loses its functionality and i have to edit cell and press ctrl+shift+enter again

 

[combo]

Array formula has to be entered this way every time you edit the cell. So it could be easier to replace value (="12-1") by reference to other cell.

combo

 

[Loomah]

an alternative to the array function (which actually works in the same way without needing ctrl+shift+ent) is
=sumproduct(--(A1:A9="12-1"))

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​