SUMIFS ONLY UNTIL the number REPEATS

[augustodelucena]

Hi folks!

I went through a non-turning-back situation with my Excel formulas and Im running in circles since then.

I got to a point where I can't go further and I beg for help.

What DO I need is very simple(I believe) and I would be very grateful to have a solution or even a denial about Excel limitations.

I have the following figures in my spreadsheet and I have been trying to use SUMIFS. However, I don't want to count values in a continous way, rather than that, I need to count the values ONLY until the number that is being counted is being repeated.

Example:

Spreadsheet from Column Q1 to Column Q6:

Q1 Controlling

Q2 8

Q3 8

Q4 8

Q5 8

Q6 8

On this case, the Excel would count the number 8 five times in this array. However, If there were more number just below the number 8, it would count them as well.


Q1 Controlling

Q2 8

Q3 8

Q4 8

Q5 8

Q6 8

Q7 5

Q7 8

What do I need is not to count them when the number being counted has stopped to appear eventhough it can be found at Q7.

For my needs the answer should be that the number 8 was counted 5 five times rather than 6.

I would appreaciate any further assistance.

Regards.

 

[Claus Busch]

Ciao Augusto,

Am Tue, 8 Apr 2014 06:23:56 -0700 (PDT) schrieb
(e-mail address removed):

Q1 Controlling

Q2 8

Q3 8

Q4 8

Q5 8

Q6 8

Q7 5

Q7 8

What do I need is not to count them when the number being counted has stopped to appear eventhough it can be found at Q7.

do it with a helper column. In R2 write 1.
In R3: =--(Q3=Q2) and copy down.
Then you can count the items with:
=COUNTIF(INDIRECT("Q2:Q"&MATCH(0,R1:R100,0)-1),Q2)
or
=COUNT(INDIRECT("Q2:Q"&MATCH(0,R1:R100,0)-1))
or
=COUNT(OFFSET($R$2,,-1,MATCH(0,R1:R100,0)-2))


Regards
Claus B.