Sum formula with multiple criteria.

If we have a large value in a table and we want to sum up the few of them based on some criteria. We do that, below is the example and the formula for that.





NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

In the above mentioned example, we have a data base containing information about sales. In the first field we have sales man name, in the second field we the product sold and in the third field we the number units sold by the person and the product respectively.

We have used an array formula and calculated total number Binders sold by John. The formula is =SUM((A2:A16="John")*(B2:B16="Binder")*(C2:C16)) once you entered this formula, do not hit on the enter butter button if you did so, the formula won’t work, because this is an array formula. We need to create an array for this formula. Creating an array is very simple. After input of the formula, hold down Shift+control and hit on the enter button. The array will be created automatically.
The above formula works in three ways. 1st it would look into the column A2 to A16 for the sales man “John”, then it would look at the second column B2 to B16 for the product binder and now we have the count of Binders sold by John and finally the formula sums the number of binders sold by John.