Formula giving wrong result
 

Why does this:
=SUMPRODUCT(--(MONTH(B$7:B$998)=1))
return 992 when the entire column B is empty?
The other 11 permtations work as one would expect, but this particular one
counts 'blank' as January!
Any ideas?

tia
--
Traa Dy Liooar

Jock

Formula giving wrong result
 

Jock,

This is because Excel recognizes the fictitious date 0 Jan 1900 and is
the number 0. There exists adequate reasoning behind this design
choice. For the time being, you can count the Jan dates excluding
blank cells with:

=SUMPRODUCT((MONTH(B$7:B$998)=1)*(B$7:B$998<""))

HTH
Kostis Vezerides

On Oct 15, 4:48 pm, Jock wrote:
Why does this:
=SUMPRODUCT(--(MONTH(B$7:B$998)=1))
return 992 when the entire column B is empty?
The other 11 permtations work as one would expect, but this particular one
counts 'blank' as January!
Any ideas?

tia
--
Traa Dy Liooar

Jock

Formula giving wrong result
 

Try =month(a1) when A1 is empty.

=SUMPRODUCT(--(isnumber(b$7:b$998)),--(MONTH(B$7:B$998)=1))



Jock wrote:

Why does this:
=SUMPRODUCT(--(MONTH(B$7:B$998)=1))
return 992 when the entire column B is empty?
The other 11 permtations work as one would expect, but this particular one
counts 'blank' as January!
Any ideas?

tia
--
Traa Dy Liooar

Jock

--

Dave Peterson

Formula giving wrong result
 

Hi Jock

From a date viewpoint, Excel treats a blank cell as 00/01/1900 hence Month
1.

Try
=SUMPRODUCT(--(MONTH(B$7:B$998)=1),--(B$7:B$998<""))

--
Regards
Roger Govier



"Jock" wrote in message
...
Why does this:
=SUMPRODUCT(--(MONTH(B$7:B$998)=1))
return 992 when the entire column B is empty?
The other 11 permtations work as one would expect, but this particular one
counts 'blank' as January!
Any ideas?

tia
--
Traa Dy Liooar

Jock

Formula giving wrong result
 

Thanks guys.

Next Q
How do I get this:
=COUNTIF($D5,(MIN($D$4:$D$15)))
to ignore zero's?
--
Traa Dy Liooar

Jock


"vezerid" wrote:

Jock,

This is because Excel recognizes the fictitious date 0 Jan 1900 and is
the number 0. There exists adequate reasoning behind this design
choice. For the time being, you can count the Jan dates excluding
blank cells with:

=SUMPRODUCT((MONTH(B$7:B$998)=1)*(B$7:B$998<""))

HTH
Kostis Vezerides

On Oct 15, 4:48 pm, Jock wrote:
Why does this:
=SUMPRODUCT(--(MONTH(B$7:B$998)=1))
return 992 when the entire column B is empty?
The other 11 permtations work as one would expect, but this particular one
counts 'blank' as January!
Any ideas?

tia
--
Traa Dy Liooar

Jock

Formula giving wrong result
 

=($D5=MIN($D$4:$D$15))*($D5<"")

HTH
Kostis

On Oct 15, 5:29 pm, Jock wrote:
Thanks guys.

Next Q
How do I get this:
=COUNTIF($D5,(MIN($D$4:$D$15)))
to ignore zero's?
--
Traa Dy Liooar

Jock

"vezerid" wrote:
Jock,

This is because Excel recognizes the fictitious date 0 Jan 1900 and is
the number 0. There exists adequate reasoning behind this design
choice. For the time being, you can count the Jan dates excluding
blank cells with:

=SUMPRODUCT((MONTH(B$7:B$998)=1)*(B$7:B$998<""))

HTH
Kostis Vezerides

On Oct 15, 4:48 pm, Jock wrote:
Why does this:
=SUMPRODUCT(--(MONTH(B$7:B$998)=1))
return 992 when the entire column B is empty?
The other 11 permtations work as one would expect, but this particular one
counts 'blank' as January!
Any ideas?

tia
--
Traa Dy Liooar

Jock