Indie has attended:
No courses
SUMIF Array formulae
=SUMSQ(IF((ISNUMBER(E2:E1305)),E2:E1305))
The above formula works as an array formula but as a normal formula it returns 0 - can you give me a detailed breakdown for what excel is doing in each case? The data in the range is a mix of numbers and NA() values
RE: SUMIF Array formulae
Hello Indie,
Thank you for your question regarding using the SUMIF Array formula.
The only way to avoid getting an error from a formula when the data includes errors such as #N/A you must convert the formula to an array using CTRL + SHIFT + Enter. Excel is not able to work with IS functions together with SUM functions without turning the formula into an array.
Also if you are using a formula that uses multiple criteria it should be activated as an array formula before the correct result will be shown.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
RE: SUMIF Array formulae
What I'm asking is what excel is doing and how excel is calculating the result in each case. I already know it works as an array but not otherwise, but I'm trying to understand the process a little better so I can try and work out for myself how and when to use array formulas and not.
The fact that that excel cannot work with SUM and IS functions together without making it an array is useful, but i'm looking for a more in depth response please.
RE: SUMIF Array formulae
Hello Indie,
Here's a link to Microsoft's explanation of array formulas.
Hope this helps...
http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
