MS Excel: Magic Sumproduct Formula

You can count or sum a particular column based on any number of criteria from other columns without ever bothering to use array function.
Here in this example, I have used both, Sumproduct and Array formula Sum to show the results from both.
Essentially, in A8 and A13 , we are finding out sum of quanity which are greater than 20 with "Sam" in Area "a" and in A10 and A15 , we are finding out count of records of quanity which are greater than 20 with "Sam" in Area "a" both by SumProduct formula and array formula of Sum(where you have to enter Ctrl+Shift+Enter to enter the formula):


You can perform many calculations using this magic Sumproduct formula without ever bothering to go for array formula.