[llong3316]

i have a spreadsheet that has the following information: Column A is Employee
and Column B is location captured, L = Upper level, A= Lower Level, and the
location is captured in sequential order, Column C indicates upper or lower
level. How can create an if statement that will count the round trip for each
employee. I.e if Jack goes from L to L then A should be 1 round trip for me.
Please help.

Column A Column B Column C
Jack L233 1
Jack L234 1
Jack A211 0
Jack A654 0
Jack L251 1
Jill L296 1
Jill L441 1
Jill L298 1
Jill A217 0
Jill L221 1

[Tim879]

You should be able to use the Sumproduct function...

I'm not sure I understand your problem completely but the following
formula may help.
=IF(ISODD(SUMPRODUCT(--($A$2:$A$11="Jack"),--($C$2:$C
$11=1))),-1,0)+SUMPRODUCT(--($A$2:$A$11="Jack"),--($C$2:$C$11=1))

The sum product part of this formula counts the number of times Jack
has taken a trip to the lower level (i.e. Col A = Jack / Col C = 1).
Then assuming that you need 2 trips to make a round trip, it just
subtracts 1 if the result is odd.

Hope this helps


On Jun 4, 12:53 pm, llong3316
wrote:
i have a spreadsheet that has the following information: Column A is Employee
and Column B is location captured, L = Upper level, A= Lower Level, and the
location is captured in sequential order, Column C indicates upper or lower
level. How can create an if statement that will count the round trip for each
employee. I.e if Jack goes from L to L then A should be 1 round trip for me.
Please help.

Column A Column B Column C
Jack L233 1
Jack L234 1
Jack A211 0
Jack A654 0
Jack L251 1
Jill L296 1
Jill L441 1
Jill L298 1
Jill A217 0
Jill L221 1