Location Cls
AL, ONEAL 22040 no
AL, BRFD 22040 no
AL, ALAB 24002 yes
AL, BRFD 24002 yes
AZ, DOUG 24002 yes
AL, BRFD 24002 yes
AL, MONT 21101 no
AL, BRFD 23000 yes
NV, APEX 23000 yes
AL, BRFD 23000 yes
MO, STGN 24002 yes
AL, BRFD 24002 yes
AL, MONT 24002 yes
AL, BRFD 24000 yes
AL, BRFD 23000 yes
AL, BRFD 24002 yes
AL, MONT 24002 yes
TX, DLS 24002 yes

The example shown below is to gather all amounts (in column not shown above)
by State, ie. all AL, AZ, etc. (1 line per state). Classes with "no" are not
wanted in the total. State is col E in example, the amount is col I.

Hope this helps.

"Shane Devenshire" wrote:

Hi,

It would help if you show us a some lines of sample data and indicate the
desired results based on that sample.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Valerie" wrote:

I need to sum cost from 11 sheets matching 2 different columns with one
column having 14 variables. I figure it's easiest to total everything for
the first match (state) and subtract the 7 variables not needed. This makes
the formula too long for the cell. Is there a way this can be done in VBA?

1st matching column is State
2nd matching column is class - there are 21 classes but I need only matches
for 7.

Can an "elimination" list be done as VBA Function? Basically, I want
everything that matches the state (1st match) but not the lines that match
any of the 7 classes in a list.

This is the first match for only 1 company that I currently have:
SUMPRODUCT(--(LEFT('AL CIP'!E$6:E$175,2)=$A6),'AL CIP'!I$6:I$175)

Any help would be greatly appreciated!!
Thanks,
Valerie