COUNTIF and cell matching the countif

[beachsideaccounting]

I am trying to make a formula and I have part of it down. Here is the working half. The time sheet has drop down lists with choices of 12 HR REG, 24 HR REG, etc. These are 12&24 hour shifts. The cell next to 12 HR REG (not always in the same cell) is the total number of hours (not always the same number). I would like to add to the COUNTIF the hour number. No two cells are ever the same. This is for a weighted average calculation on a paycheck.

=COUNTIF('Time Sheet'!B24:H30, "12 HR REG")

 

[gehusi]

Perhaps an example would help clarify what your calculation should look like.

 

[beachsideaccounting]

I hope this makes sense, I have listed out

Clock IN | Clock Out | Pay Rate (drop down list) | Total Hours

*under each column is a different time, pay rate, and total hours worked.

On a second sheet I am trying to calculate the weighted average of our pay and I would like to pull over the total hours each day that matches that daily Pay Rate. There could be a different rate and hours 2x in one day. (paramedic/firefighter type work)

 

[gehusi]

Assuming the columns you have listed there correspond to A, B, C, and D, you'd enter

=sumif(C2:C100,"=12",D2:D100)

Adjust the range to suit your needs, and replace the 12 with whatever code designates the pay rate you are looking for.

 

[beachsideaccounting]

that is working perfectly thank you

If I may ask, why will my calculations not come out correctly.

When I attempt to add the total of one cell that has a formula in it with another cell that has a formula in it, the balance is off by a few cents.

The formula is ='Time Sheet'!J31*Summary!B16
*Time sheet number is 40, and Summary B16 is 12.56

If I use the above formula I get $502.48

If I manually enter the numbers 40*12.56 I get 502.40 (Correct number)

 

[gehusi]

They should calculate the same. Click on each of those cells, go to cell formatting, and change it to "Number", and set the number of decimals to 3 or 4. I suspect there may be some trailing digits after the decimal point that are causing it to calculate to 502.48.

 

[Aladin Akyurek]

I hope this makes sense, I have listed out

Clock IN | Clock Out | Pay Rate (drop down list) | Total Hours

*under each column is a different time, pay rate, and total hours worked.

On a second sheet I am trying to calculate the weighted average of our pay and I would like to pull over the total hours each day that matches that daily Pay Rate. There could be a different rate and hours 2x in one day. (paramedic/firefighter type work)

Try to provide some examples.

08:15 | 16:15 | 6.8 | 8:00
09:00 | 12:00 | 4.8 | 3:00

Given the foregoing, what result do you expect? If this is not the right data, try to provide a small appropriate set along with the expected result.

 

[beachsideaccounting]

Aladin, I have the correct formula now for the previous questions. My only problem is I believe is the rounding issue. I tried changing everything to number, accounting, etc. Also changed decimal points to 2/3/4 still doesn't work but if you just manually enter the numbers it comes out correct.

My overall goal is to create a spreadsheet or multiple shift under multiple payrates, even in the same day, based on a weighted average of pay.

 

[Aladin Akyurek]

Aladin, I have the correct formula now for the previous questions. My only problem is I believe is the rounding issue. I tried changing everything to number, accounting, etc. Also changed decimal points to 2/3/4 still doesn't work but if you just manually enter the numbers it comes out correct.

My overall goal is to create a spreadsheet or multiple shift under multiple payrates, even in the same day, based on a weighted average of pay.

What is the correct formula you invoke? What result does it return? Try to be specific by referring to some sample.

 

[beachsideaccounting]

Aladin, I am having a heck of a time even explaining here is the link to the actual spreadsheed in my dropbox public folder. The main issue is highlighted on the summary page.

Cell B18 must be B16*"the amount of regular hours worked that week".

Cell B19 must be B16+1/2 the rate in B16 * "the amount of OT hours worked that week"

http://dl.dropbox.com/u/30988164/AMR%20Time%20Sheet.xlsx