Hi :

I had been trying to replicate the result as per row 8 below but failed. Can anyone help ?

1.) Replicate Row 3 column data left to right till the number 0 ignore the rest of the data after 0 for the 1st set of data correspondingly to row 8.
2.) Replicate row 4 column data start after where row 3, 1st set of data ends till number 0 as per (1) to row 8 correspondingly.
3.) Repeat the same for row 5 and 6 onward as per steps (2 & 1) till column K.
4.) Count from left to right row 8 the number of columns it takes to reach 0. Update count result to row 10.
5.) Count the number of occurrence the row 10 data have that is >3 to row 12.

1 A B C D E F G H I J K
2 Date 1 2 3 4 5 6 7 8 9 10
3 Ivan 0 23 12 14 0 21 11 55 0 21
4 Susan 15 12 25 0 17 20 26 0 12 14
5 Henrik 11 0 15 15 26 35 31 0 15 12
6 Tommy 12 15 24 15 0 11 15 18 48 25
7


8 Result 0 12 25 0 26 35 31 0 48 25
9
10 Summary 1 3 4 2
11
12 >3 1

Regards
Loran

I started out with an attempt at understanding your description, and filling in where I needed to with guesses. While I was writing those guesses in this space, the light dawned. Here's what I think you mean. (Really, it'd have been a lot easier had you included more punctuation.)Is that an accurate description of what you need?

Yes that is exactly what I need.

And do you need just the final answer (ie 1), or are the intermediate numbers important too?

All this is easy enough to do in VBA. How to do it with formulae...well, I'm sure it can be done, but it'd take some work. Are you up for a programmed solution, or do you want it in formulae?

Here's a sample solution using formulae. The first part, to get your row 8 (my 9) turned out to be simpler than I thought. The whole thing is a little kludgy; I'd probably write a worksheet function, myself, especially if the actual data is much, much larger.

Attached Files

x.xlsx (9.8 KB, 13 views)

Thanks for the write-up this option may work. Below are my reply on the queries posted and refinement require.

1.) The intermediate numbers in Row 8, Row 10 and Row 12 are require.

2.) When I changed the data in the attached file for Ivan and Susan as per below, row 8 result did not change correspondingly.

Ivan 23 15 0 15 22 0 55 40 10 21
Susan 15 0 15 30 0 15 10 75 13 12
R8 0 12 25 0 26 35 31 0 48 25

3.) Noticed that in row 7 the 1st data is preset as 1 is that the reason why the data do not change when the conditions are changed ?

4.) The data is larger than the example that can be expand accordingly once the formula is establish.

2) When I change data for Susan and Ivan, the results rows change immediately. Do you have calculation set on manual or automatic?

3) R7C2 is set to 1 because it can't add anything to the previous column (which is text), and the first number in that row should always be 1. But I don't think this should cause any trouble; my theory (see above) is that something else is causing that problem.

Had re-open the attachment and rerun again. You are right the data changes accordingly when inputs conditions were changed.

Great, the system formulated works I had learn something new. Previously I was trying to use consolidate condition which may not be suitable.

Thanks for the great job, you help is greatly appreciated.