[mso] Re: CountIF help
- From: "Ray Blake" <ray@xxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Mon, 15 Sep 2003 21:46:32 +0100
Robert,
There are a couple of possible approaches to this, but first we need to
get over the problem of pre-1900 dates. The only way I can think of
overcoming this is to rip the date portion of the string out, add (say)
200 to it, then use the result as the year portion to convert it with
the rest of the string into a date you could then manipulate and convert
back as necessary.
Assuming that's an acceptable approach, your first option would be an
array formula containing a SUM or a COUNT with an IF for each of the two
conditions. The conditional sum wizard from the Tools menu would be a
good place to start building the formula if in doubt.
But if you need a large number of array formulae in your sheet, you'll
encounter the big speed problem that arrays have and in that event, I'd
suggest you look to the D-functions instead. As luck would have it,
Linda published an article of mine on this very subject in her ABC
newsletter this month:
http://personal-computer-tutor.com/abc3/v28/vol28.htm
Ray Blake
_____________
This email is from Ray Blake, Head of Software Design, GR Business
Process Solutions. It is confidential and intended for the addressee
only. The contents are private and may be legally privileged. If you
receive this email in error we would be grateful if you would advise the
sender and delete the email from your system.
For more information on the services that we offer please visit us at
our website: - www.grbps.com
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Robert Carneal
Sent: 15 September 2003 19:57
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: CountIF help
Colin raises a good point. Let me change his inquiry a little; what if
the
user wanted to find all dates between two text dates?
This is not valid Excel code, but will serve to show what I am trying to
do.
IF D1 > "1822-10-20" AND D1 < "1891-01-01" then Kount = Kount +1
At the bottom of the column, it will tell me how many fit that equation.
What would be Excel equivalent be for that? One major problem is the
dates
are before Excel's preferred starting date.
Robert
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the
quotes) in the subject line.
Or, visit the group's homepage and use the dropdown menu. This will
also allow you to change your email settings to digest or vacation (no
mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group,
send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an
invitation with instructions. Once you are a member of the files group,
you can go here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
