What am I doing wrong he

=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

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" wrote in message
...
What am I doing wrong he

=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

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 he

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

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=540337

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" wrote in message
...
What am I doing wrong he

=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

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 he

=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

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" wrote in message
...
What am I doing wrong he

=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

=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 he
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" wrote in message
...
What am I doing wrong he

=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

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 he
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" wrote in message
...
What am I doing wrong he

=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