Sumproduct question

[Barb Reinhardt]

What am I doing wrong here:

=SUMPRODUCT(AL3:AL84>="3/1/2006")*(AP3:AP84=6)

I want to count the number of times AL3:AL84 >= 3/1/2006 AND AP3:AP84 = 6.
I keep getting ZERO when this is a non zero number. I have entered it with
CTRL SHIFT ENTER (I think that's it) and it has {} around it.

Thanks,
Barb Reinhardt

[SteveG]

Barb,

Try this,

=SUMPRODUCT((AL3:AL84>=DATE(2006,3,1))*(AP3:AP84=6))

You were missing some parenthesis and when you put "3/1/2006" in, the formula is looking for a text string equal to that but excel sees dates as numbers not text. You could also type the date in another cell and refer to it in the formula rather than using the DATE formula.

HTH

Steve

[Peo Sjoblom]

You need more parenthesis and you need to convert the text "3/1/2006"

=SUMPRODUCT((AL3:AL84>=--"3/1/2006")*(AP3:AP84=6))

although this is better

=SUMPRODUCT((AL3:AL84>=--"2006-03-01")*(AP3:AP84=6))

since it will work with different regional settings

no need to array enter it


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> What am I doing wrong here:
>
> =SUMPRODUCT(AL3:AL84>="3/1/2006")*(AP3:AP84=6)
>
> I want to count the number of times AL3:AL84 >= 3/1/2006 AND AP3:AP84 = 6.
> I keep getting ZERO when this is a non zero number. I have entered it
> with
> CTRL SHIFT ENTER (I think that's it) and it has {} around it.
>
> Thanks,
> Barb Reinhardt
>
>

[bpeltzer]

I'd try =sumproduct(--(al3:al84>=date(2006,3,1),--(ap3:ap84=6)) with no array
required. "3/1/2006" is forcing a text comparison; date(2006,3,1) converts
to Excel's numeric representation of the date 3/1/06.

"Barb Reinhardt" wrote:

> What am I doing wrong here:
>
> =SUMPRODUCT(AL3:AL84>="3/1/2006")*(AP3:AP84=6)
>
> I want to count the number of times AL3:AL84 >= 3/1/2006 AND AP3:AP84 = 6.
> I keep getting ZERO when this is a non zero number. I have entered it with
> CTRL SHIFT ENTER (I think that's it) and it has {} around it.
>
> Thanks,
> Barb Reinhardt
>
>

[Dave Peterson]

I'd use:

=sumproduct(--(al3:al84>=date(2006,3,1)),--(ap3:ap84=6))

And ap3:ap84 contains the number 6, not the string '6 (as text), right?

Barb Reinhardt wrote:
>
> What am I doing wrong here:
>
> =SUMPRODUCT(AL3:AL84>="3/1/2006")*(AP3:AP84=6)
>
> I want to count the number of times AL3:AL84 >= 3/1/2006 AND AP3:AP84 = 6.
> I keep getting ZERO when this is a non zero number. I have entered it with
> CTRL SHIFT ENTER (I think that's it) and it has {} around it.
>
> Thanks,
> Barb Reinhardt

--

Dave Peterson

[Bernard Liengme]

Try =SUMPRODUCT(--(AL3:AL84>=date(2006,3,1)),--(AP3:AP84=6))
I am assuming you use US date convention
Confirm with simple ENTER
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> What am I doing wrong here:
>
> =SUMPRODUCT(AL3:AL84>="3/1/2006")*(AP3:AP84=6)
>
> I want to count the number of times AL3:AL84 >= 3/1/2006 AND AP3:AP84 = 6.
> I keep getting ZERO when this is a non zero number. I have entered it
> with
> CTRL SHIFT ENTER (I think that's it) and it has {} around it.
>
> Thanks,
> Barb Reinhardt
>
>

[Barb Reinhardt]

Thanks everyone. Can someone explain what the -- in the sumproduct formula
means?

"Bernard Liengme" wrote:

> Try =SUMPRODUCT(--(AL3:AL84>=date(2006,3,1)),--(AP3:AP84=6))
> I am assuming you use US date convention
> Confirm with simple ENTER
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Barb Reinhardt" <[email protected]> wrote in message
> news:[email protected]...
> > What am I doing wrong here:
> >
> > =SUMPRODUCT(AL3:AL84>="3/1/2006")*(AP3:AP84=6)
> >
> > I want to count the number of times AL3:AL84 >= 3/1/2006 AND AP3:AP84 = 6.
> > I keep getting ZERO when this is a non zero number. I have entered it
> > with
> > CTRL SHIFT ENTER (I think that's it) and it has {} around it.
> >
> > Thanks,
> > Barb Reinhardt
> >
> >
>
>
>
>

[Dave Peterson]

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Barb Reinhardt wrote:
>
> Thanks everyone. Can someone explain what the -- in the sumproduct formula
> means?
>
> "Bernard Liengme" wrote:
>
> > Try =SUMPRODUCT(--(AL3:AL84>=date(2006,3,1)),--(AP3:AP84=6))
> > I am assuming you use US date convention
> > Confirm with simple ENTER
> > --
> > Bernard V Liengme
> > www.stfx.ca/people/bliengme
> > remove caps from email
> >
> > "Barb Reinhardt" <[email protected]> wrote in message
> > news:[email protected]...
> > > What am I doing wrong here:
> > >
> > > =SUMPRODUCT(AL3:AL84>="3/1/2006")*(AP3:AP84=6)
> > >
> > > I want to count the number of times AL3:AL84 >= 3/1/2006 AND AP3:AP84 = 6.
> > > I keep getting ZERO when this is a non zero number. I have entered it
> > > with
> > > CTRL SHIFT ENTER (I think that's it) and it has {} around it.
> > >
> > > Thanks,
> > > Barb Reinhardt
> > >
> > >
> >
> >
> >
> >

--

Dave Peterson

[Barb Reinhardt]

Thank you. I've wondered about that for a while.

"Dave Peterson" wrote:

> =sumproduct() likes to work with numbers. The -- stuff changes trues and falses
> to 1's and 0's.
>
> Bob Phillips explains =sumproduct() in much more detail here:
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> And J.E. McGimpsey has some notes at:
> http://mcgimpsey.com/excel/formulae/doubleneg.html
>
> Barb Reinhardt wrote:
> >
> > Thanks everyone. Can someone explain what the -- in the sumproduct formula
> > means?
> >
> > "Bernard Liengme" wrote:
> >
> > > Try =SUMPRODUCT(--(AL3:AL84>=date(2006,3,1)),--(AP3:AP84=6))
> > > I am assuming you use US date convention
> > > Confirm with simple ENTER
> > > --
> > > Bernard V Liengme
> > > www.stfx.ca/people/bliengme
> > > remove caps from email
> > >
> > > "Barb Reinhardt" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > What am I doing wrong here:
> > > >
> > > > =SUMPRODUCT(AL3:AL84>="3/1/2006")*(AP3:AP84=6)
> > > >
> > > > I want to count the number of times AL3:AL84 >= 3/1/2006 AND AP3:AP84 = 6.
> > > > I keep getting ZERO when this is a non zero number. I have entered it
> > > > with
> > > > CTRL SHIFT ENTER (I think that's it) and it has {} around it.
> > > >
> > > > Thanks,
> > > > Barb Reinhardt
> > > >
> > > >
> > >
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>