Sum Mutliple criteria Using the mid Function

**kgibson20** · 09-06-2011, 03:40 PM

Please refer to attachment for specific data
I need two formulas, one to sum hours or dollars based on each specific discipline while excluding cost codes for that discipline where the cost code at =mid(cost code,4,3) which will be 888. The second one will sum all the 888 for each discipline.

I was trying to use a sumproduct which has worked well in multiple criteria sums but I can't figure out how to use the mid formula along with it.

**ChemistB** · 09-06-2011, 03:46 PM

Okay, what defines a discipline? Once we figure that out, then you want all hours or dollars (which is it? both?) summed for that discipline unless the cost code contains 888 as the 4-6 characters?

**NBVC** · 09-06-2011, 03:50 PM

I would add a helper column to pull the code out.

e.g.

=MID(A2,4,3) copied down

Then create pivot tables for each so that the unique values are pulled and summed.

http://peltiertech.com/Excel/Pivots/

**NBVC** · 09-06-2011, 03:51 PM

The you can add/remove columns, move the columns around to summarize differently, etc.

**Harley2** · 09-06-2011, 03:54 PM

If I am following, using a sumproduct for each disc
to remove the 888 entries use
SUMPRODUCT(--(MID(A2:A456,4,3)<>"888"),--(B2:B456="CE"),D2:D456)

to get just the 888 for each disc use
SUMPRODUCT(--(MID(A2:A456,4,3)="888"),--(B2:B456="CE"),D2:D456)

Both these are for the Bill Amount for Disc "CE"

**kgibson20** · 09-06-2011, 03:55 PM

Originally Posted by ChemistB

Okay, what defines a discipline? Once we figure that out, then you want all hours or dollars (which is it? both?) summed for that discipline unless the cost code contains 888 as the 4-6 characters?

The Discipline are in column B, pick one as an example. As far as hours or dollars it doesn't matter, pick one and I'll figure out the other. I'm mostly concerned about the syntax of the formula.

**kgibson20** · 09-06-2011, 03:56 PM

Originally Posted by Harley2

If I am following, using a sumproduct for each disc
to remove the 888 entries use
SUMPRODUCT(--(MID(A2:A456,4,3)<>"888"),--(B2:B456="CE"),D2:D456)

to get just the 888 for each disc use
SUMPRODUCT(--(MID(A2:A456,4,3)="888"),--(B2:B456="CE"),D2:D456)

Both these are for the Bill Amount for Disc "CE"

Thanks Harley this is what I was looking for.