SUMPRODUCT using a list?

[Valerie]

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

 

[Shane Devenshire]

Hi,

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

 

[That One Guy]

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]

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.

 

[Roger Govier]

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)

 

[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

 

[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