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

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

I think the easiest solution in your case would be a pivot table.

Put the State and Class in row (State then Class).
Use Sum of value for whatever value you're trying to calculate in the total

Then, simply uncheck the classes or states that you don't need.

Just remember that you have to manually refresh (click the red
explanation mark) when you go and make changes to your data.

I hope this helps.

Regards,

That One Guy.

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

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

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

I use Pivot tables quite a bit but haven't created one using multiple sheets
as the source. Every sheet contains a different amount of rows so how do I
consolidate 11 sheets into one source data?

Thanks!
Valerie

"That One Guy" wrote:

I think the easiest solution in your case would be a pivot table.

Put the State and Class in row (State then Class).
Use Sum of value for whatever value you're trying to calculate in the total

Then, simply uncheck the classes or states that you don't need.

Just remember that you have to manually refresh (click the red
explanation mark) when you go and make changes to your data.

I hope this helps.

Regards,

That One Guy.

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

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