**From the Not Just Numbers blog:**

Let me start with an apology for leaving it so long without a post. It’s been a busy time work-wise and, in addition, I’ve taken up running to finally lose the excess weight and run the Great North Run half marathon next month. After spending the last 20 years or so doing very little exercise while sitting in front of a computer screen, I decided I had to do something about it – and raise some money for a good cause along the way!

OK! Excuses over! I’ve got a very powerful but often misunderstood function to tell you about.

On the face of it, SUMPRODUCT does a very simple thing, it multiplies arrays together and sums the results. Unless you’re a serious mathematician, you’re probably thinking that this is not something you’ve ever had a desire to do! But bear with me.

First of all, here is a simple explanation of how it works:

=SUMPRODUCT({5,6,4},{7,4,12}) returns 107 because:

You can enter up to 255 arrays like this (as long as they are all the same size) and SUMPRODUCT will multiply them then sum the totals as above. That’s basically what SUMPRODUCT does. Now let’s look at why this is very useful.

First of all, the SUMPRODUCT function allows you to work with arrays without entering the function using Ctrl+Alt+Enter, which means that you don’t run the risk of accidentally clicking into the cell and clicking enter, then wondering why your array formula has stopped working. If you don’t know what an array formula is ignore this point, as you don’t need to worry about it with SUMPRODUCT. If you do, then you’ll know what I’m talking about!

More importantly, the arrays entered into SUMPRODUCT can be formulae that result in arrays. The formula above could have been entered as:

*Incidentally, if we don’t multiply this by the C2:C8 range we can use this as an alternative to COUNTIFS*

*i.e.*

*=SUMPRODUCT(–(A2:A8=”North”),–(B2:B8=”A”))*

*is the same as:*

*=COUNTIFS(A2:A8,”North”,B2:B8,”A”)*

**If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.**