Find/count consecutive entries in a row in excel

Your range starts in column D (the FOURTH column). Therefore, in the section that returns the column of the array, you need to take away 3 so that it starts counting at 1 instead of 4 (i.e. D=1, E=2, etc.).

Also why do I get negative results on some cells

No idea. I haven't bothered trying to get my head around the exact nature of what it is you are trying to do, just the mechanics of the formula.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.