Array formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim - 08 Jul 2006 19:53 GMT

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.

Reply to this Message

Trevor Shuttleworth - 08 Jul 2006 21:22 GMT

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

> 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
[quoted text clipped - 13 lines]
> "False" or zero. Any help would be greatly appreciated. I'm using Excel
> 2000, SP3.

Reply to this Message

Rate this thread: