Any experts on "countif" formula using nested conditions

C

Chuck

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
 
B

Bob Phillips

=SUMPRODUCT((A1:A300>=Date(2003,04,01)*(A1:A300<=DATE(2004,04,07))

--

HTH

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

Jason Morin

SUMPRODUCT is usually the method of choice when counting
or summing based on multiple criteria:

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

HTH
Jason
Atlanta, GA
 
B

Bob Phillips

Jason Morin said:
SUMPRODUCT is usually the method of choice when counting
or summing based on multiple criteria:

=SUMPRODUCT((A1:A300>="4/1/04"*1)*(A1:A300<="4/7/04"*1))
Click to expand...

Novel approach Jason,<G>
 
N

Norman Harker

Hi Bob & Jason!

But not as robust as using DATE because those dates can be one of 6
alternatives depending upon Regional settings and the double digit
year interpretation setting.

But:
=SUMPRODUCT((A1:A300>="4-Jan-2004"*1)*(A1:A300<="4-Jul-2004"*1))

Now that's robust and easier than DATE but still suffers the defect of
hard coding in formulas.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
B

Bob Phillips

Don't be an old grouch Norman<vbg>.

It was an interesting novel approach which can be expanded upon (as you
showed). It's up to us to take it forward into interesting areas if it can
be, or drop it quietly.

Bob
 
N

Norman Harker

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
B

Bob Phillips

Norman Harker said:
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.
Click to expand...

I guess it must be me then Not hbelped by Arsenal losing. At least it wasn't
to an Oz team!
But, "Yes!" it was novel and a good way of hard coding those pesky
date strings (or time strings).
Click to expand...

Precisely what I thought. Must remember it for future reference.
 
N

Norman Harker

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
B

Bob Phillips

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)
 
B

Bob Phillips

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)
 
N

Norman Harker

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

Frank Kabel

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.
 
F

Frank Kabel

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

Norman Harker

Hi Bob!

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

The -- strikes again!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

Frank Kabel

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.
Click to expand...

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.
Click to expand...

They will never accept these changes :)

Regards
Frank
 
N

Norman Harker

Hi Frank!

Re: "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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

Frank Kabel

[...]
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.
Click to expand...

</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
 
Top