Array formula

[Jim]

In columns B and C there are 22,000+ entries. Column B contains numbers from
1 thru 27 in random order. Column C contains the letters A, B, and C in
random order.

These letters and numbers represent subsets of jobs and were entered over
several weeks.

I need to be able to enter an array formula in Column D adjacent to a label
that will give me the total number of jobs for A1. Then I'd like to copy
this formula to cells below for the other combinations of letters and
numbers. Once I have the array formula for set A1 the copying part should be
no problem. I know an array formula has to be entered using the Control,
Shift and Enter keys.

I've tried several different array formula and either get an error or
"False" or zero. Any help would be greatly appreciated. I'm using Excel
2000, SP3.

 

[Trevor Shuttleworth]

One way:

=SUMPRODUCT(($B$1:$B$22000=--RIGHT($A1,LEN($A1)-1))*($C$1:$C$22000=LEFT($A1,1)))

All on one line.

Note that this does not *need* to be entered as an array formula

Regards

Trevor