Count cells in range that restarts at intervals
 

I have a table like this:
Row Name Y/N? No. of Y
1 Adam Y 1
2 Adam Y 2
3 Adam N 2
4 Adam N 2
1 Brian Y 1
2 Brian N 1
3 Brian N 1
4 Brian Y 2

Is there a formula I can put for "No. of Y" that counts the no. of times "Y"
appears for Adam from the first "Adam" row up to the current row? The
counting should restart for Brian and not accumulate from Adam's total.

Note that the starting point for each person is when "Row" = 1.

Can a pivot table help?

Understood that perfectly! :D
So why don't I have to use ctrl+shift+enter since this is an array formula?

"Rowan" wrote:

The -- is a double unary minus which forces the Sumproduct function to
Convert True and False answers to 1's and 0's. More confused now?

There is a great explanation of Sumproduct at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

and further explanation of the double unary minus from Bob Phillips and
Harlan Grove at
http://tinyurl.com/bv42x

Hope this helps
Rowan

=Sumproduct() behaves like an array formula, but you don't need the
ctrl-shift-enter stuff.

That doesn't answer why, though. I'd guess it's just the way the developers
designed the function.



Melissa wrote:

Understood that perfectly! :D
So why don't I have to use ctrl+shift+enter since this is an array formula?

"Rowan" wrote:

The -- is a double unary minus which forces the Sumproduct function to
Convert True and False answers to 1's and 0's. More confused now?

There is a great explanation of Sumproduct at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

and further explanation of the double unary minus from Bob Phillips and
Harlan Grove at
http://tinyurl.com/bv42x

Hope this helps
Rowan

--

Dave Peterson