RE: Count cells in range that restarts at intervals

[Rowan]

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

"Melissa" wrote:

> Fantastic! Worked like a charm...
> but can you explain what the "--" means?
>
> "Rowan" wrote:
>
> > If you data starts in Row 2 with the Names in column A and the Y/N in column
> > B then in C2 put the formula:
> >
> > =SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2="Y"))
> >
> > and copy down.
> >
> > Hope this helps
> > Rowan
> >
> > "Melissa" wrote:
> >
> > > 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?

[Rowan]

[Melissa]

Fantastic! Worked like a charm...
but can you explain what the "--" means?

[Melissa]

[Melissa]

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

[Dave Peterson]

=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.

--

Dave Peterson