sum from table with criteria

[jeff]

I have the following data in 4 columns: Branch, Code,
Count, Amount.

I would like to add the count and amount of certain codes
together that make a category (and do this for each
branch).

I export this info from our mainframe and the # of rows
can change (add new codes, delete unused codes)from month
to month.

I've lookes at array formulas, lookups, and some
functions, but can't seem to do it. The only thing I came
up with was to do a SUMIF function, but I would be adding
about 20 SUMIF's in 1 formula to get all the codes for say
one category.

Any help would be greatly appreciated,
Jeff

P.S. Excel 97 SR-1

 

[Jeff]

You may be able to make a macro that adds in all your
SUMIF's automatically. This way if it adds in 500 SUMIF's
what's the difference.
This is probably (def.) not the best way to do this, but
it should work.

 

[Myrna Larson]

Have you looked at using a pivot table and grouping the codes that make up each category?

Or you could create a lookup table to translate each code to a category, and put a VLOOKUP
formula in a "helper" column on the right. Once you've done that, you should be able to use
SUMIF and COUNTIF.