Any experts on "countif" formula using nested conditions

Hi Norm,

It certainly seems that way.

I still happily recall being at Seven Sisters Road many years ago when we
beat Spurs 1-0 to win the League title, and the cup on the following
Saturday - good days!

Dennis Wise v Roy Keane, all grace, elegance, and skill!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Norman Harker" wrote in message
...
Hi Bob!

Arsenal? Are they going to snatch defeat from the jaws of victory this
season? But I'm a Spurs supporter and all we care about is where we
are relative to Arsenal; not much cheer for years!

Millwall odds must be pretty good!
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Norman,

Just played around some more with Jason's formula. Originally, I tested it
exactly as Jason had entered it

=SUMPRODUCT((A1:A300="4/1/04"*1)*(A1:A300="4/7/04"*1))

and with my test data it returned a 2. But of course, Jason is American, so
he was talking 1st April to 7th April. I got the 'correct' answer, so I
assumed Excel defaulting to US style dates. So I then change the formula,
but not the data, to

=SUMPRODUCT((A1:A300="1/4/04"*1)*(A1:A300="7/4/04"*1))

and it still returned the same answer. The problem obviously was inadequate
test data was insufficient, just 2 dates to prove it
added up, so I added another of 8th April, and Jason's version returned 3,
mine returned 2, which was correct.

So, Jason's version suffers from the old US/European date problem, and as it
stands can not be offered as a solution as the poster could be using either
format. Of course, the DATE(year,month,day) problem does not suffer from
this.

The obvious solution is to suggest putting the date in a cell and compare
against that, but that is always satisfactory, sometimes it is good to have
the date in the formula. On this basis, I suggest the following version,
which although I can't test with US dates, I would assume works okay with
both date styles

=SUMPRODUCT((A1:A300="01-Apr-2004"*1)*(A1:A300="07-Apr-2004"*1))

This can be made more flexible to cater for a fixed date in the current
year with

=SUMPRODUCT((A1:A300=("01-Apr-"&YEAR(TODAY()))*1)*(A1:A300=("07-Apr-"&YEAR
(TODAY()))*1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Norman Harker" wrote in message
...
Hi Bob!

"An old grouch"? Not me! at least not after the 3rd cup of coffee in
the morning, 2nd beer after lunch and 1st Single Malt after dinner.

But, "Yes!" it was novel and a good way of hard coding those pesky
date strings (or time strings).

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Bob!

Help warns against strings being used in various date functions. In my
view the warnings are a little too strong and would be best replaced
by advice to use "unequivocal formats".

I did some work on this and the following strings all appear to be
acceptable and unequivocal for at least three English language
versions of Excel (English, Australian, US).

12 January 2002
12-January-2002
12/January/2002
12 Jan 2002
12-Jan-2002
12/Jan/2002
2002-01-12
2002/01/12
2002/1/12

Of these, only the final three will be largely "Regional Settings
immune" because the other six require English language versions. (FAOD
includes USA). Since number 7 (yyyy-mm-dd) is the ISO8601:2000
approved separated date format, I'd go for that one. In Canada, or
Europe I think it would be asking for trouble insisting on an English
language version of Excel.

I've tested:
=SUMPRODUCT((A1:A300="2004-04-01"*1)*(A1:A300="2004-04-07"*1))

This seems to work OK. It's a bit foreign to most of us although it is
being brought in for EEC purposes and is the long standing method used
in China.

For fixing to the current year, my preference would be to go the whole
hog and use DATE.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Bob
in addition to Norman's reply you'll run into problems using this kind
of formulas in non-English Excel version. Using a German version a
formula like
=SUMPRODUCT((A1:A300="01-Apr-2004"*1)*(A1:A300="07-Apr-2004"*1))
will work.

But if you need a date in March your formula would look like
=SUMPRODUCT((A1:A300="01-Mar-2004"*1)*(A1:A300="07-Mar-2004"*1))
and this will return an error in my German version. I had to use
=SUMPRODUCT((A1:A300="01-März-2004"*1)*(A1:A300="07-März-2004"*1))

Same problem would occur for May ('Mai' in German) and December
('Dez'). So IMHO I would use the DATE function if I have to ensure the
formulas have to work in all Excel versions.


--
Regards
Frank Kabel
Frankfurt, Germany

"Bob Phillips" schrieb im
Newsbeitrag ...
Norman,

Just played around some more with Jason's formula. Originally, I
tested it
exactly as Jason had entered it

=SUMPRODUCT((A1:A300="4/1/04"*1)*(A1:A300="4/7/04"*1))

and with my test data it returned a 2. But of course, Jason is
American, so
he was talking 1st April to 7th April. I got the 'correct' answer, so
I
assumed Excel defaulting to US style dates. So I then change the
formula,
but not the data, to

=SUMPRODUCT((A1:A300="1/4/04"*1)*(A1:A300="7/4/04"*1))

and it still returned the same answer. The problem obviously was
inadequate
test data was insufficient, just 2 dates to prove it
added up, so I added another of 8th April, and Jason's version
returned 3,
mine returned 2, which was correct.

So, Jason's version suffers from the old US/European date problem,
and as it
stands can not be offered as a solution as the poster could be using
either
format. Of course, the DATE(year,month,day) problem does not suffer
from
this.

The obvious solution is to suggest putting the date in a cell and
compare
against that, but that is always satisfactory, sometimes it is good
to have
the date in the formula. On this basis, I suggest the following
version,
which although I can't test with US dates, I would assume works okay
with
both date styles

=SUMPRODUCT((A1:A300="01-Apr-2004"*1)*(A1:A300="07-Apr-2004"*1))

This can be made more flexible to cater for a fixed date in the
current
year with


=SUMPRODUCT((A1:A300=("01-Apr-"&YEAR(TODAY()))*1)*(A1:A300=("07-Apr-"
&YEAR
(TODAY()))*1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Norman Harker" wrote in message
...
Hi Bob!

"An old grouch"? Not me! at least not after the 3rd cup of coffee
in
the morning, 2nd beer after lunch and 1st Single Malt after dinner.

But, "Yes!" it was novel and a good way of hard coding those pesky
date strings (or time strings).

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and
Arguments)
available free to good homes.

Hi Norman
see my reply to Bob's post. In addition your last three date 'styles'
works at least in a German Excel version

--
Regards
Frank Kabel
Frankfurt, Germany

"Norman Harker" schrieb im Newsbeitrag
...
Hi Bob!

Help warns against strings being used in various date functions. In
my
view the warnings are a little too strong and would be best replaced
by advice to use "unequivocal formats".

I did some work on this and the following strings all appear to be
acceptable and unequivocal for at least three English language
versions of Excel (English, Australian, US).

12 January 2002
12-January-2002
12/January/2002
12 Jan 2002
12-Jan-2002
12/Jan/2002
2002-01-12
2002/01/12
2002/1/12

Of these, only the final three will be largely "Regional Settings
immune" because the other six require English language versions.
(FAOD
includes USA). Since number 7 (yyyy-mm-dd) is the ISO8601:2000
approved separated date format, I'd go for that one. In Canada, or
Europe I think it would be asking for trouble insisting on an English
language version of Excel.

I've tested:
=SUMPRODUCT((A1:A300="2004-04-01"*1)*(A1:A300="2004-04-07"*1))

This seems to work OK. It's a bit foreign to most of us although it
is
being brought in for EEC purposes and is the long standing method
used
in China.

For fixing to the current year, my preference would be to go the
whole
hog and use DATE.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Bob!

=SUMPRODUCT((A1:A300=--"2004-04-01")*(A1:A300=--"2004-04-07"))

The -- strikes again!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Frank!

Some time I'll have to get hold of other language versions of Excel to
check out some of the differences and acceptabilities. I've had a few
confirmations in the past.

It's good to get a confirmation that 2002-01-12, 2002/01/12, 2002/1/12
are all working in German. I previously did some pretty intensive
testing for three English language settings and these 9 were the only
unequivocal ones I found. Only the magic 3 acceptable in a non-English
version;
I think it was Ron de Bruin who tested them in Dutch.

My own view is that for date entry in workbooks, for hard coding in
functions and for formatting, we are heading for potential problems if
we go outside the 9 forms. As much as it might be difficult to change
our customs, there is certainly a lot of logic behind using
ISO8601:2000 yyyy-mm-yy. The English and Australians were dragged
kicking and screaming to accept metrication so I don't harbour a lot
of hopes of seeing it soon.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Frank Kabel" wrote in message
...
Hi Norman
see my reply to Bob's post. In addition your last three date
'styles'
works at least in a German Excel version

--
Regards
Frank Kabel
Frankfurt, Germany

"Norman Harker" schrieb im Newsbeitrag
...
Hi Bob!

Help warns against strings being used in various date functions. In
my
view the warnings are a little too strong and would be best
replaced
by advice to use "unequivocal formats".

I did some work on this and the following strings all appear to be
acceptable and unequivocal for at least three English language
versions of Excel (English, Australian, US).

12 January 2002
12-January-2002
12/January/2002
12 Jan 2002
12-Jan-2002
12/Jan/2002
2002-01-12
2002/01/12
2002/1/12

Of these, only the final three will be largely "Regional Settings
immune" because the other six require English language versions.
(FAOD
includes USA). Since number 7 (yyyy-mm-dd) is the ISO8601:2000
approved separated date format, I'd go for that one. In Canada, or
Europe I think it would be asking for trouble insisting on an
English
language version of Excel.

I've tested:
=SUMPRODUCT((A1:A300="2004-04-01"*1)*(A1:A300="2004-04-07"*1))

This seems to work OK. It's a bit foreign to most of us although it
is
being brought in for EEC purposes and is the long standing method
used
in China.

For fixing to the current year, my preference would be to go the
whole
hog and use DATE.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and
Arguments)
available free to good homes.

Hi Norman

Some time I'll have to get hold of other language versions of Excel
to
check out some of the differences and acceptabilities. I've had a few
confirmations in the past.

I know these kind of problems. I still have to convince my boss that I
need different language versions of MS Office to do this kind of
testing :-(
Believe me you'll encounter VERY interesting difficulties regarding
delimiters, function names, etc. vbg


[...]
My own view is that for date entry in workbooks, for hard coding in
functions and for formatting, we are heading for potential problems
if
we go outside the 9 forms. As much as it might be difficult to change
our customs, there is certainly a lot of logic behind using
ISO8601:2000 yyyy-mm-yy. The English and Australians were dragged
kicking and screaming to accept metrication so I don't harbour a lot
of hopes of seeing it soon.

They will never accept these changes :-)

Regards
Frank

Hi Frank!

"you'll encounter VERY interesting difficulties regarding
delimiters, function names, etc. vbg"

Don't I know it! I had a lot of problems once in Indonesia when one
computer in a lab had settings for , instead of . as the decimal
indicator. Didn't it just have to be the slowest student in the class
and of course it took some time before I found it *was* the computer
and not her.

Function List translations are coming along now and this might help
quite a few people who are struggling to use two versions. But it
looks like I might have to borrow (or steal) some language versions to
complete the job.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

[...]
Function List translations are coming along now and this might help
quite a few people who are struggling to use two versions. But it
looks like I might have to borrow (or steal) some language versions
to
complete the job.

/wishmode on
I think it would be a good thing if MS would add an option to choose
the function name language as a setting. And if they're doing this
change the behaviour of the Analysis Toolpak Add-in to also translate
function names automatically.
/wishmode off

I know this won't happen in the near future as they have to implement
blinking cells first vbg

Frank