98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » SUMIF Array formulae
SUMIF Array formulae
Resolved · Medium Priority · Version 2003
Fri 6 Jul 2012, 12:08Delegate Indie said...
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
Sat 7 Jul 2012, 14:42Trainer Rodney said...
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
Mon 9 Jul 2012, 10:23Delegate Indie said...
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.
Mon 9 Jul 2012, 20:15Trainer Rodney said...
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
Mon 16 Jul 2012: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Counting BlanksSome times you want to check if there are cells missing data in your range. You can use the COUNTBLANK FUNCTION to acheive this. It is =COUNTBLANK(Range). Note Cells with formulas that return "" (empty text) are also counted. Cells with zero values are not counted. |
