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?
A:
I'm not sure if this is mac-specific. I have always used
=COUNTA(G$19:G$1034)
for this purpose.
e.James
2009-06-20 23:19:06
A:
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
2009-06-20 23:20:39
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
2009-06-21 07:40:57
A:
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
2009-06-20 23:44:08