5

I want to be able to sum up the number of cells in a range that have a non-null value in them. On a PC running XP and Excel I entered =SUM(IF(G$19:G$1034="",0,1)) and it spit out the correct answer. Now the same spreadsheet on a Mac running Excel 2004 for Mac gives that a #VALUE! error. Any thoughts on why?

ZygD
  • 22,092
  • 39
  • 79
  • 102
Patrick
  • 53
  • 1
  • 1
  • 3

4 Answers4

10

I realize that the post was mede a long time ago, but i recently and regularly have the same problem - and I always seem to forget the solution: Select the cell or range of cells that contains the array formula, press CONTROL+U to edit the formula, and then press ⌘+RETURN.

Viktor S.
  • 12,736
  • 1
  • 27
  • 52
RLi
  • 101
  • 1
  • 3
2

You need to enter it as array (matrix) formula. After typing the formula don't hit enter but hit either

CMD+SHIFT+ENTER or CTRL+SHIFT+ENTER

Where CMD is the apple key. I'm not sure what the keycombination is on mac. But you can check in the help file. Entering array formula

jitter
  • 53,475
  • 11
  • 111
  • 124
1

It might be settings that control the separator character. That "," is the separator on one machine and something else, for example ";" is the separator character on the other.

Shiraz Bhaiji
  • 64,065
  • 34
  • 143
  • 252
  • As far as I know, the separator is locale-dependent and is used only for formula entry and display; it's not actually stored in the compiled-into-RPN formula saved in the file. In any case it's easy to determine: type in =SUM(1,2) in one cell and =SUM(1;3) in another; one will produce 3 and the other will produce an error dialogue box -- you won't get as far as #VALUE! because the formula is syntactically incorrect. – John Machin Jun 21 '09 at 07:40
0

I'm not sure if this is mac-specific. I have always used

=COUNTA(G$19:G$1034)

for this purpose.

e.James
  • 116,942
  • 41
  • 177
  • 214