You can use the SUMPRODUCT function to create advanced analysis of a data set. For example, you can calculate the number of time a sales rep has made sales in a particular region.

  A B C D E F  
1 Sales Rep Region Sales Quarter   Formula Result
2 John North $2,500 1   =SUMPRODUCT((array1,criteria1)*(array2,criteria2))  
3 Paul South $2,450 2   =SUMPRODUCT((A2:A10=”john”)*(B2,B10=”north”)) 3
4 Ringo North $9,900 3      
5 George South $3,450 4      
6 John North $3,475 1      
7 Paul South $4,280 2      
8 Ringo North $3,830 3      
9 George South $2,130 4      
10 John North $3,562 1      

The formula in F3 counts the number of instances where the name “John” in A2:A10 is associated with “North” in B2:B10.

Note: The same result can be achieved with the COUNTIFS function