I need to sum amounts from 11 sheets that meet 2 different columns on each
sheet. Each sheet is a different company and all sheets have information in
the same format. The first criteria is straight forward match to a state.
The second criteria, however consists of 21 different classes of which I only
want 14 of the 21. I am currently using a SUMPRODUCT formula to gather the
totals by state, but the second criteria makes the formula too long for the
cell (to gather unwanted since there are only 7 and subtract from total). Is
there a way to write a VBA function that would select only lines that match
the 1st criteria and 2nd criteria of 7 classes?

Here's an example of the first part that I am currently using:
SUMPRODUCT(--(LEFT('AL CIP'!E$6:E$175,2)=$A6),'AL CIP'!I$6:I$175)
$A6=the state I'm matching to.

Thanks,
Valerie

This is a duplicate post. Microsoft gave an error msg so I submitted again
before realizing it actually did go through.

"Valerie" wrote:

I need to sum amounts from 11 sheets that meet 2 different columns on each
sheet. Each sheet is a different company and all sheets have information in
the same format. The first criteria is straight forward match to a state.
The second criteria, however consists of 21 different classes of which I only
want 14 of the 21. I am currently using a SUMPRODUCT formula to gather the
totals by state, but the second criteria makes the formula too long for the
cell (to gather unwanted since there are only 7 and subtract from total). Is
there a way to write a VBA function that would select only lines that match
the 1st criteria and 2nd criteria of 7 classes?

Here's an example of the first part that I am currently using:
SUMPRODUCT(--(LEFT('AL CIP'!E$6:E$175,2)=$A6),'AL CIP'!I$6:I$175)
$A6=the state I'm matching to.

Thanks,
Valerie