# Help with a formula needed please - sumifs formula not working - maybe another function needed?

#### SandyG

##### New Member
Good day. I'm setting up a worksheet that will track a contractor's daily labor, times it by their hourly rate, and provide a total cost for each week worked. That part works fine. The weekly costs are then associated with a project(or cost center). I have added an extra column to show which project(or cost center) the weekly costs are associated with.

After that, I'm having a problem. I want to write a formula that will look at each week, identify the project(or cost center) and sum the weekly amount for that project(or cost center).

The weekly total and the project number are on the same line.

Here is an example - Jane Doe worked 40 hours for the week ending 4/7/19. The total cost for the week is \$3,000, and the project ID is NIS00702. John Doe also worked 40 hours for the week ending 4/7/19, with total costs of \$3500, and the same project ID.

The formula should be able to find the project (NIS00702), and "sum" all costs that are associated with it for that week ending.

I tried a sumifs formula, but that is not working, because even though the total costs for the week are on the same line, they are not right next to each other (i.e., B13 - Regular Hours, C13 - OT Hours, D13 - Project ID, (Next Contractor) E13 - Regular hours, etc....

Any help in the right direction would be great! Thank you!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### goesr

##### Well-known Member
Hi SandyG - Hope this helps. A very simple example of SUMIFS has the formula =SUMIFS(A1:A3,B1:B3,"red") in Cell C1, with Cell A1 = 40, Cell A2 = 50, Cell A3 = 60, Cell B1 = red, Cell B2 = blue and Cell B3 = red. The SUMIFS looks for "red" in column B and then adds 40 + 60 to get 100. You should be able to do a similar thing in your example. Sometimes people forget to put quotes around the criteria (using red rather than "red") and that confuses the formula. Good luck.

#### kweaver

##### Well-known Member
SandyG - are all the contractors strung across ONE line based on the week ending? Where's the total cost for each?

Is it like this:

W/E Person RegHrs OTHrs RateReg RateOT Total Proj etc., etc.
4/7/19 Jane 40 0 75 100 3000 NIS00702 John 20 10 100 150 3500 NIS00702 ...

Or does the sheet have the W/E down the A column, person in B, etc., etc.?
If it's way, it's rather easy.

Column A is contractor name
Column B is HOURS
Column C is WeekEnding Date
Column D is Total
Column E is Proj Number

If G3 has the proj number to be found and H3 has the W/E date, I3 could be this formula:

Code:
``=SUMIFS(D2:Dn,C2:Cn,H3,E2:En,G3)``

You'd have to change the n to however many lines the listing is in.

Last edited:

#### kweaver

##### Well-known Member
If, in fact, your data is going horizontally, I think I have a solution to that format.
This version only tested w/three people but could be extended.

Last edited:

Replies
1
Views
68
Replies
9
Views
110
Replies
1
Views
249
Replies
5
Views
65
Replies
6
Views
264

1,127,165
Messages
5,623,115
Members
415,955
Latest member
ssheldon2021

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back