SUMIF and COUNTIF don’t work in spreadsheets created through excel on iPad

[willyak]

Excel spreadsheets created on computer with SUMIF and COUNTIF formulas work perfectly. Creating a new spreadsheet on iPad and using these formulas don’t work, return zero. Is the excel app for iPad more limited than the version for the computer?

[#2]

@willyak 

Excel on iPad is definitely limited, but in your case perhaps you have texts instead of numbers which are ignoring on sum.

[#3]

@Sergei Baklan 

i’ve checked, all are numbers, it seams the “Y” is not being recognized. For example:

=SUMIF(d4:d25,”Y”,e4:e25)

This formula works on the excel version for the computer but not on the excel version for iPad. Further, if the spreadsheet is created on a computer with this formula and then I access the same file with the iPad, the formulas work. My problem, I work mainly on iPad and don’t readily have access to a computer.

[#4]

@willyak Not a frequent Excel Mobile user, but could test it on my iPhone. It seems that the keyboard on the mobile version defaults to italic (or curly) quotation marks, rather than straight ones. On the mobile keyboard, long-press or press and hold the quotation mark button. More options appear. Choose the straight quotation mark and your formula will work.

Don't ask me why the app defaults to a non-functional character. Couldn't find how to change this default behaviour and it doesn't seem to be related to language settings either. I have sent feed-back to MS via the mobile app as I think this is quite silly. Wait and see.

[#5 · Accepted solution]

@willyak UPDATE: On your iOS device, goto Settings, General, Keyboard and turn off "Smart Punctuation". That stops defaulting to curly quotation marks.

[#6]

@Riny_van_Eekelen 

Mystery solved! Thank you very much!

Mahalo,

Will

[#7]

@willyak You're welcome!

[#8]

Thank you for this! I had a coworker experience a problem where a formula worked on PC but not on iPad and this is why.

[#9]

By turning off the smart punctuation as described on this thread - TOTALLY WORKS! Lifesaver. Thanks!