Need Help with a sum formula based off criteria within the same tab

[brijen2005]

Good Morning,

I was wondering if anyone could guide me in the right direction with a formula. I need a formula that sums multiple cells based off certain criteria.

I need the sum of "cost" for each employee based on county. For example below I have John's name in column (A) several times however John could work in numerous counties within a month's time frame. How can I get the total cost (E) for John per County?

(A)Employee: John,(D) Hours: 40, (E) Cost: $150, (F) County: Clay, (L) Gross: $5706; (M) Per Hour: $36.91
(A) Employee: Sue,(D) Hours:10, (E) Cost $40, (F) County: Clinton, (L) Gross: $3481, (M) Per Hour: $34.81
(A) Employee: Bob, (D) Hours: 20, (E) Cost: $90, (F) County: Clay, (L) Gross: $4144, (M) Per Hour: $36.24
(A) Employee: John, (D) Hours:12, (E) Cost: $50, (F) County: Clay, (L) Gross: $3914, (M) Per Hour: $34.91
(A) Employee: John, (D) Hours: 34, (E) Cost: $115, (F) County: Clinton, (L) Gross: $5014, (M) Per Hour: $36.03

Formula I prefer however seems a little more complicated:
(aware it won't pull the same outcome for numbers)
Gross ( L) divided by the total number of hours each employee (sum of D based on employee name in column A) multiplied by the hours per county (F) for that specific person (A). For example: John's gross is $14,634 (Sum of "L" based off name in column A) divided by his 86 total hours (sum of D based off name in column A) = $170.16, then that number multiplied by 52 (sum of the hours he worked for that specific county "F") = $8,848.47

Sorry if this doesn't make sense

[davesexcel]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[PaulM100]

Try this: =SUMIF($A$1:$A$5,$K$2,$E$1:$E$5)/SUMIF($A$1:$A$5,K$2,$B$1:$B$5)*SUMIFS($B$1:$B$5,$D$1:$D$5,L$1,$A$1:$A$5,$K$2)
I used A-F as the columns of data.
K2 - John
L1 - County

[brijen2005]

Thank you everyone, I'm very new to this so let's hope I am attaching this correctly.

The excel sheet has two tabs... one for before and one for after

[Glenn Kennedy]

Can you explain how you derived ANY of the values in column M????

[brijen2005]

I just inserted #'s in column M. They aren't actuals as I was doing a mock spreadsheet as an example.

[PaulM100]

In M:=J2/SUMIF($A$2:$A$10,I2,$D$2:$D$10)
in N for hours: =SUMIFS($D$2:$D$10,$A$2:$A$10,I2,$F$2:$F$10,$O$2)
for total cost epr employee per county is: =SUMIFS($E$2:$E$10,$A$2:$A$10,I2,$F$2:$F$10,$O$2) but I didn't knew where you wanted to add the info
O2 is the county Clay

[Glenn Kennedy]

Ohhh That's really helpful in solving a logical problem. Random numbers as example solutions.

[brijen2005]

Thank you! I will try these. I had been playing around with the SUMIF but couldn't nail it down

[brijen2005]

My apologies... I was requesting help with that formula which is why I put random numbers. I understand your frustration. I know I could have put =J3/(D3+D6+D7+D10) however given I will have 200+ lines in the spreadsheet that will change each week I know I'll need a more complex formula that pulls it by name vs actual cell.

[davesexcel]

You should still show the desired results on the sample workbook

[Glenn Kennedy]

I spent some time trying to figure out where the values came from. I could not work it out at all. It was logical (I thought) to assume that they were a couple of manually calculated results.

Bear that in mind next time you post a sample sheet!!