CODEWITHSUNDEEP
excelmacos
Patrick
Patrick

Reputation: 53

Excel formulas giving #VALUE! error when ported to Mac

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?

Upvotes: 5

Views: 13921

Answers (4)

RLi
RLi

Reputation: 101

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.

Upvotes: 10

jitter
jitter

Reputation: 54605

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

Upvotes: 2

Shiraz Bhaiji
Shiraz Bhaiji

Reputation: 65421

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.

Upvotes: 1

James Eichele
James Eichele

Reputation: 119164

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

=COUNTA(G$19:G$1034)

for this purpose.

Upvotes: 0

Related Questions