comparing totals in 2 column

  • Thread starter Thread starter richzip
  • Start date Start date
R

richzip

I have a very large spreadhseet. The spreadsheet looks similar to the one
below. I want to compare the totals (by employee) in column B with the
totals (by employee) in column C. If the total in column B is larger, then I
want to transfer those values to column D. If the total in column C is
larger, then I want to transfer THOSE values to column D.

As you can see, for ID 1 ..the total of column C is greater, so those values
are carried over to column D. For ID 2 ..the total of column B is greater,
so those values are carried over.

Since there are a large number of employees, how do I set up a formula to
compare these values by employee and make the transfer? Thanks!

A B C D
1 ID Sked Act Greater
2 1 5 6 6
3 1 7 5 5
4 1 2 4 4
5 2 3 4 3
6 2 10 2 10
7 2 5 4 5
8 2 6 9 6
 
I can do this .sort of ..with the following formula in cell D2, then copied
down to the subsequent rows for that ID:
=IF(SUM($B$2:$B$4)>SUM($C$2:$C$4),B2,C2)

But ..this would require editing the formula at the top line of each ID,
because the row references change, and each ID has a variable number of rows.
There are over 400 ID's so I am hoping to find a formula I can type into
just the top row,and copy to the bottom of the worksheet.
 
I'm following you on this, but, what should happen if the totals are equal?

1...1...2
1...2...1
1...1...1

Both totals are 4
 
In the meantime, this will do what you want:

=IF(SUMIF(A:A,A2,B:B)>SUMIF(A:A,A2,C:C),B2,C2)
 
Thank you! That worked perfectly. If the totals are the same, I will use
the values from column C, and it looks like this formula does that.

Thanks again!
 
Back
Top