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.
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?
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
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/
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
The you can add/remove columns, move the columns around to summarize differently, etc.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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"
Harley2
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
Originally Posted by ChemistB
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.
Thanks Harley this is what I was looking for.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks