Count instances based on two criteria

[Stuart Mantel]

I am building a testing log.
Col A contains the test number (values 1-4).
Col D contains the test result codes (values 1-11).

I can count how many results were from test 1 and I can count how many
result codes were 1, 2, 3, etc. using countif().

I am trying to count how many of each result occured in each test. For test
pass 1 how may results were code 1, code 2, etc.

Thanks,
Stuart

 

[John C]

=SUMPRODUCT(--(A2:A100=testnumber),--(D2100=testresult))

Assuming your data is in rows 2 through 100, this is the formula used for
each occurrance. Therefore, if you were looking for all 44 possible outcomes,
you would need 44 formula. or you can just set testnumber and testresult to
specific cell references, where you can then decide which test/result combo
you are looking for.

Hope this helps.

 

[Duke Carey]

Sounds like a good case for a pivot table. Put the test numbers as the
Column Labels, the result codes as the row labels, and for values use the
Count of the result codes