Counting number of forwarding and sending back

[Guest]

Dear Excel(lent) users,

I am looking for the following:

I am counting the times an incident goes back and forth from one team till
another. An incident is registered and team 1 picks it up, when they are
ready they forward it to team 3, they forward it to team 4, but they send it
back to team 3.

In this case the number of sending back and forward is 1.

An incident is registered and team 1 picks it up, when they are ready they
forward it to team 3, they forward it to team 4, but they send it back to
team 3, they sent it back to team 1, who in their turn send it back to team 4.

In this case the number of sending back and forward is 3 (because the normal
sequence is maintained, but from team 4 it is ping-ponged back between other
teams).

The solution I have chosen is as follows:
Column A till J is the sequence of the teams as the incident is forwarded
Column O is the summation per line of the number of teams involved per
incident
[=IF(B3<>"";Countif(E3:N3);"")]
Column P would be the calculation of the above description, which I am
looking for.

Thanks for helping me out here !!

 

[Guest]

I think the problem is with the COUNTIF in your formula below. It is missing
a parameter.

I don't understand why it is looking in cells E3:N3, when your real data
only goes up to column J.

Try this instead:
Number of pingpongs is =IF(B3="",0,COUNTA(E3:J3))
(this gives a max number of pingpongs as 6 (entries in all cells E3 to J3).

You might need to play around with it a bit to get it right.

HTH

 

[Guest]

Dear Alllen,

No I am not looking to improve [=IF(B3<>"";Countif(E3:N3);"")] As that is a
different formula for a different situation (I should have ommitted it in my
text, sorry)
What I am looking for is:
I am counting the times an incident goes back and forth from one team till
another. An incident is registered and team 1 picks it up, when they are
ready they forward it to team 3, they forward it to team 4, but they send it
back to team 3.

A1 B1 C1 D1
Team 1 Team 3 Team 4 Team 3

In this case the number of sending back and forward is 1.

An incident is registered and team 1 picks it up, when they are ready they
forward it to team 3, they forward it to team 4, but they send it back to
team 3, they sent it back to team 1, who in their turn send it back to team 4.

In this case the number of sending back and forward is 3 (because the normal
sequence is maintained, but from team 4 it is ping-ponged back between other
teams).

A1 B1 C1 D1 E1 F1
Team 1 Team 3 Team 4 Team 3 Team 1 Team 4

Hope this makes more sense.
--
** Fool on the hill **

I think the problem is with the COUNTIF in your formula below. It is missing
a parameter.

I don't understand why it is looking in cells E3:N3, when your real data
only goes up to column J.

Try this instead:
Number of pingpongs is =IF(B3="",0,COUNTA(E3:J3))
(this gives a max number of pingpongs as 6 (entries in all cells E3 to J3).

You might need to play around with it a bit to get it right.

HTH
--
Allllen

Dear Excel(lent) users,

I am looking for the following:

I am counting the times an incident goes back and forth from one team till
another. An incident is registered and team 1 picks it up, when they are
ready they forward it to team 3, they forward it to team 4, but they send it
back to team 3.

In this case the number of sending back and forward is 1.

An incident is registered and team 1 picks it up, when they are ready they
forward it to team 3, they forward it to team 4, but they send it back to
team 3, they sent it back to team 1, who in their turn send it back to team 4.

In this case the number of sending back and forward is 3 (because the normal
sequence is maintained, but from team 4 it is ping-ponged back between other
teams).

The solution I have chosen is as follows:
Column A till J is the sequence of the teams as the incident is forwarded
Column O is the summation per line of the number of teams involved per
incident
[=IF(B3<>"";Countif(E3:N3);"")]
Column P would be the calculation of the above description, which I am
looking for.

Thanks for helping me out here !!