Creating a Counting Grid

Problem:

Range A2:B7 contains the letters A to C and a corresponding number from 0 to 5. Each combination can appear any number of times.
Range D3:D6 contains the letters A to D. The numbers 0 to 5 are listed in cells E2:J2.
We want to create a formula that will count the number of rows in the range A2:B7 containing each letter-number combination shown in the range D2:J6.

Solution:

Use the SUMPRODUCT function as shown in the following formula and enter it in cell E3, then copy the formula to the entire grid (cells E3:J6):
=SUMPRODUCT(($A$2:$A$7=$D3)*($B$2:$B$7=E$2))

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube