countif multiple criteria

[Iamwhoiam]

This is tough one, one of my culumns..

O_ACCOUNTCODE
EUSMD386
EUSMD458
EUSMD303
DEP06111
EUSMD303
WILLIAMS02
EUSMD386
BG001
EUSMD386
BBEN01
DEP06111
DEPTM021
DEPTM021
DEP06111
BOSBOU01
DEPMOD001
DEP06111
SIHILE
EUSMD516
EUSMD516
EUSMD523
ALB001
SARADEP SOC01
AGATHA
AGATHA
EUSMD110
EUSMD651
OBC01
EUSMD303
EUSMD651
EUSMD110
EUSMD659
WILLIAMS02
EUSMD386
SA LW001
EUSMD104

In this column as above, all of these acount belong in different categories,
such as
RETAIL SALE
CORP SALES
GOV SALES
CHANNEL SALES
Thereare 4000 entries and about 200 different accounts

The problem is that you can count by using countif:
example: =countif(d2:d2000,"EUSMD*")+countif(d2:d2000,"WILLIAM*")..etc
but my problem is there are about 50 diiferent accounts for each category.
What would be the easiest way to count the acounts that are in the retail
group, using a VBA function or something to that effect. I already know that
excel can't do this by itself, as there is a 255 char limit on the length of
the formula. also updating the formula is pretty painful.
Can anyone suggest anything here?

 

[bpeltzer]

I'd put a lookup table on a separate worksheet (call it Sheet2 for arguments
sake) that lists each unique account in column A and the corresponding
category next to it in column B. Then, add a helper column next to your
400-entry list. If your list begins in d2, then in e2 enter the formula
=vlookup(e2,Sheet2!A:B,2,0). Copy that formula down through all relevant
rows.
Then base your countif on the categories listed in column E instead of the
accounts in column D.
My two cents. --Bruce