Hi, Roger,

I don't have a column stating "yes" or "no" in my worksheets, I want to
write a formula or VBA to exclude those classes from my result. I already
have the formula you show for everything, now I need one for those I don't
want included to subtract from the everything formula.

Thanks,
Valerie

"Roger Govier" wrote:

Hi Valerie

If I have understood you request correctly, on a second sheet list the
States you want in column A, starting at A2, enter in B2
=SUMPRODUCT((Sheet1!$E$2:$E$1000=$A2)
*(Sheet1!$G2:$G1000<"no")*Sheet1!$I$2:$I$1000)

--
Regards
Roger Govier

"Valerie" wrote in message
...
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