Counting values by verifying two cells in excel

If we have values in several rows and columns, and if we need to take count of some values referring two columns we can do that.




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

We have used an array formula and calculated total number Binders sold by John. The formula is =SUM((A2:A16="John")*(B2:B16="Binder") 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 two ways, 1st it would look for the value (Name) John in the sales man field and 2nd it would look for count of binder sold by him and returns the value.