• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combining same rates

AdamH61686

New Member
Hello,
for my payroll system I have guys working at different jobs and different rates. Sometimes the rates for a particular job is the same as another. In this case I would like the hours at these rates to be combined.
upload_2017-2-21_12-36-48.png
In the picture above, I have an example of what I'm working with, I've formatted the cells in column E to turn yellow when the rates are the same as any other in the section. What I would like is for the corresponding cells in column H to add together. so in this example I'd like cell H8 to read 4 and the others to read 0. I've tried a SWITCH function but can't seem to figure it out.
THANKS!
 
In merge cell H8, enter formula and copy down :

=IF((COUNTIF(E9:E$1000,E9)>1)*(COUNTIF(E$9:E9,E9)=1),COUNTIF(E9:E$1000,E9),0)

Regards
Bosco
 
In merge cell H8, enter formula and copy down :

=IF((COUNTIF(E9:E$1000,E9)>1)*(COUNTIF(E$9:E9,E9)=1),COUNTIF(E9:E$1000,E9),0)

Regards
Bosco

Thanks Bosco, but that turned all cells below E8 into 0. I only want the yellow cells to combine into 1 since they all have the same pay rate. If you look at E11 for example, it has a different rate then E8, I want that to stay the way it is. I don't know if it matters but the values in column H will vary as they are the number of hours worked. I have them all as 1's just while I was testing things out. I know it's a tricky problem sorry about that, but I appreciate the help!
 
......What I would like is for the corresponding cells in column H to add together. so in this example I'd like cell H8 to read 4 and the others to read 0.......
"......What I would like is for the corresponding cells in column H to add together. so in this example I'd like cell H8 to read 4 and the others to read 0......."

Please clarify this statement and requirement

Thanks Bosco, but that turned all cells below E8 into 0. I only want the yellow cells to combine into 1 since they all have the same pay rate. If you look at E11 for example, it has a different rate then E8, I want that to stay the way it is. I don't know if it matters but the values in column H will vary as they are the number of hours worked. I have them all as 1's just while I was testing things out. I know it's a tricky problem sorry about that, but I appreciate the help!

The formula only applied in column H and does not affect column E.

Regards
 
Last edited:
"......What I would like is for the corresponding cells in column H to add together. so in this example I'd like cell H8 to read 4 and the others to read 0......."

Please clarify this statement and requirement



The formula only applied in column H and does not affect column E.

Regards
To clarify, The values in column E are the rate of pay for particular jobs which vary depending on the county in which they are located. These values are auto populated from another sheet where the job is selected. In some cases there are multiple jobs that have the same rate of pay. The H Column is the amount of hours each worker has for that particular job. This sheet is what we submit to our payroll company, they have asked that if there are multiple occurrences of the same pay rate that they be combined. So in the example above(Which is hypothetical) Milton Ayala has worked 1 hour at 8 different jobs, 4 of which have a pay rate of $60.09. I'm wondering if there is a way to make it so that if a pay rate in column E shows up more than once (In a group of 16 rows, as the rows after that are for another worker) then the hours in column H that are in the same rows as those duplicate rates would add together in the first occurrence and show 0 for any subsequent occurrences of that duplicate pay rate.
I hope that clears it up a little. I know it's really complicated, that's why I've turned to you for help.
 
Back
Top