How do I compare 2 text ranges and total?

[Kathryn J Bittman]

I need a formula that will compare 2 separate text values and report based
on specific conditions. Here is an example:

B2:B120 (type) will have data entries of:
RC
RS
N
PC

C2:C120 (pgm) will have data entries of:
F
T
M
E

On my summary, I need to know how many pgms are of which type. Such as E has
2 PC, 1 RS, 1 RC, 0N. My summary is on another worksheet.

 

[RagDyeR]

On your summary sheet, create a list of your *unique* PGMs, say A2 to A5.
List your *unique* TYPEs in Row1, from B1 to E1.

In B2, enter this formula:

=SUMPRODUCT((Sheet1!$C$2:$C$120=$A2)*(Sheet1!$B$2:$B$120=B$1))

Now, copy this formula across to E2.

Select B2 to E2, and drag down to copy this range to E5.

This will give you a matrix of the counts of each of the data items.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I need a formula that will compare 2 separate text values and report based
on specific conditions. Here is an example:

B2:B120 (type) will have data entries of:
RC
RS
N
PC

C2:C120 (pgm) will have data entries of:
F
T
M
E

On my summary, I need to know how many pgms are of which type. Such as E has
2 PC, 1 RS, 1 RC, 0N. My summary is on another worksheet.

 

[Kathryn J Bittman]

Thank! Worked great.

 

[Ragdyer]

Thanks for the feed-back.