Any experts on "countif" formula using nested conditions

My parochialism Frank. I accounted for US/European date styles, and then
forgot language.

Back to the drawing board.

--

HTH

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

"Frank Kabel" wrote in message
...
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.

"Norman Harker" wrote in message
...

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.

You obviously haven't lived in England for a long time if you think we have
accepted metrication.

"Frank Kabel" wrote in message
...

They will never accept these changes :-)

Hey you, we're more European than the Europeans these days - Tony tells us
(regularly!).

Bob Phillips wrote:
My parochialism Frank. I accounted for US/European date styles, and
then forgot language.

Back to the drawing board.

Bob
always the same with the islanders in the EU vbg
Frank

Bob Phillips wrote:
"Frank Kabel" wrote in message
...

They will never accept these changes :-)

Hey you, we're more European than the Europeans these days - Tony
tells us (regularly!).

rotflol

Frank

Of course, this is the same as *1, and is fast becoming the accepted
standard, surprised that Harlan hasn't jumped in.

I resolve to adopt the yyyy-mm-dd style from now on, even if my countrymen
don't.

This has been good! Jason's little tid-bit has led to some interesting
thoughts and a New Year's resolution for me.

Thanks everyone.

Bob

"Norman Harker" wrote in message
...
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 Bob!

Know what you mean!

I could understand changing the system but to pick one invented by the
French is unforgivable gdr

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

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

Hi Bob!

Agreed that's what we should do but...

"a New Year's resolution for me"

Old habit die hard! England abandoned New Year starting on 26-Mar in
1752 (which if you add the 11 days for Gregorian calendar reform is
the basis for your FY beginning on 6th-Apr.)

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Bob Phillips" wrote in message
...
Of course, this is the same as *1, and is fast becoming the accepted
standard, surprised that Harlan hasn't jumped in.

I resolve to adopt the yyyy-mm-dd style from now on, even if my
countrymen
don't.

This has been good! Jason's little tid-bit has led to some
interesting
thoughts and a New Year's resolution for me.

Thanks everyone.

Bob

"Norman Harker" wrote in message
...
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.

Nobody told me!

Bob

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

Agreed that's what we should do but...

"a New Year's resolution for me"

Old habit die hard! England abandoned New Year starting on 26-Mar in
1752 (which if you add the 11 days for Gregorian calendar reform is
the basis for your FY beginning on 6th-Apr.)

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Bob Phillips" wrote in message
...
Of course, this is the same as *1, and is fast becoming the accepted
standard, surprised that Harlan hasn't jumped in.

I resolve to adopt the yyyy-mm-dd style from now on, even if my
countrymen
don't.

This has been good! Jason's little tid-bit has led to some
interesting
thoughts and a New Year's resolution for me.

Thanks everyone.

Bob

"Norman Harker" wrote in message
...
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.

Excellent, thank you gentlemen


-----Original Message-----
Hi Chuck!

See Bob and Jason's solutions. You can't use multiple
conditions
within COUNTIF or SUMIF.

Generally we use SUMPRODUCT although you can often use
two COUNTIF
functions:

=COUNTIF(A1:A300,"="&"4-Jan-2004")-COUNTIF
(A1:A300,""&"4-Jul-2004")

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

Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"Chuck" wrote in message
...
I'm trying to get the count of the number of cells that
contain a multiple condition using dates.

Count the number of instances in cells A1 to A300 which
are between or equal to the dates 04/01/04 and 04/07/04

countif A1:A300 =04/01/04 and =04/07/04




.