# When should SUM() be used as an array formula vs. not in Excel

How come {=SUM(A2:A10*B2:B10)} works, but without CTRL+SHIFT+ENTER it doesn't?

I'm trying to learn more about array formulas, but I wish there was some guide about what goes on behind-the-scenes. Doesn't A2:A10*B2:B10 simply spit out an array of multiplications? In which case, SUM should be able to just consume that? But here we're using the Array Formula syntax, which means we are using the AF version of SUM? Does that mean it iterates through each multiplication, and SUM them up, and stitch them together somehow? Really confused.

• Yes as an array it does something like this `(A2*B2)+(A3*B3)+....` – Scott Craner Jun 16 '16 at 21:22

## 1 Answer

You don't need to array enter if the sum contains simple ranges or numbers. You can avoid array entering with Sumproduct().

To see how Excel processes the different formulas use the Evaluate Formula tool on the Formulas ribbon.

See a little animation about it here

• If you type =SUM(A1:A3*B1:B3) it gives you #VALUE! if you don't CTRL+SHIFT+Enter. But if you type =SUM(A1:A3) it doesn't give you #VALUE! why? – philipkd Jun 17 '16 at 22:42
• Read my answer. Specifically the first sentence. `=SUM(A1:A3)` contains a simple range to sum. `=SUM(A1:A3*B1:B3)` contains a calculation performed on two ranges. Watch the animation. – teylyn Jun 17 '16 at 23:04
• Thanks, looks like I'm getting #VALUE! for other reasons. – philipkd Jun 17 '16 at 23:25