[mso] Re: Count - Melody Kennedy

That was just my assumption.  You can make the inner IF want you need.  =
If you have a value for the supplemental insurance and if it is zero if =
they don't have any, you could use:

=3DSUM(IF($B$2:$B$22<30,IF($A$2:$A$22 > 0,1,0),0))    Which just returns =
a 1 for each client that matches.

if you leave the cell blank for no supplemental insurance you could use

=3DSUM(IF($B$2:$B$22<30,IF(not(isblank($A$2:$A$22)),1,0),0))    Which =
just returns a 1 for each client that matches.   The cells have to be =
really blank.  If you tend to replace numbers in the cells by clicking =
on the cell and hitting the space bar, get used to clicking on the cell =
and hitting delete instead.

Remember CTRL-SHIFT-ENTER to end the edit/entry operation.

Robert Tillotson

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of Melody Kennedy
Sent: Monday, August 13, 2007 11:27 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Count - Melody Kennedy


In the supplemental life column do the words true or false have to be in
there or can there be a the amount of life insurance in the column?   =
=3D20

Melody Kennedy
AGA, Inc. Employee Benefits=3D20
One Union Square; Suite 303
Chattanooga, TN  37402
Phone: (423) 424-2853
Fax: (423) 664-0107=3D20

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Tillotson, Robert N (US SSA)
Sent: Monday, August 13, 2007 10:26 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Count - Melody Kennedy

Melody,

Try using an Array formula with one result.
Assuming Column B has the age of the client and column A has true or =
=3D3D
false for supplemental life policy:

Click the cell you want the result in
enter =3D
=3D3D3DSUM(IF($B$2:$B$22<30,IF($A$2:$A$22=3D3D3DTRUE,$A$2:$A$22*1,0),0)) =
=3D3D
on the formula bar=3D3D20
press CTRL-SHIFT-Enter to get the array formula inserted (on the formula
=3D3D
bar you will see {} around the formula

If you edit the formula later you will need to remember to use =3D3D
CTRL-SHIFT-Enter to end the edit or you won't have an array formula

Of course you will adjust the formula for your spreadshet columns but =
=3D
=3D3D
since the file is unavailable in this list I used these assumptions.

This works because true =3D3D3D 1 (and false =3D3D3D 0) in Excel so True =
* 1 =3D
=3D3D3D
=3D3D
1 (the result of the inner IF: IF($A$2:$A$22=3D3D3DTRUE,$A$2:$A$22*1,0)) =
=3D
and
=3D3D
gives you a number to sum.


Robert Tillotson

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of Melody Kennedy
Sent: Monday, August 13, 2007 9:22 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Count


Content-Type: text/plain;
        charset=3D3D3D"us-ascii"
Content-Transfer-Encoding: quoted-printable
I am trying to set up a formula that will count the number of people
with supplemental life that are under the age of 30.  Can anyone help?
I have tried several If formulas but I can't seem to get it to work.

=3D3D3D20

Melody Kennedy

AGA, Inc. Employee Benefits=3D3D3D20

One Union Square; Suite 303

Chattanooga, TN  37402

Phone: (423) 424-2853

Fax: (423) 664-0107=3D3D3D20

=3D3D3D20




-- No attachments (even text) are allowed --
-- Type: application/vnd.ms-excel
-- File: Test.xls
-- Desc: Test.xls


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
=3D3D
or MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the =
=3D
=3D3D
dropdown menu at the top.  This will allow you to unsubscribe your email
=3D3D
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
=3D3D
group.  This group will not allow for posting of emails, but will allow
=3D3D
you to join and share problem files, templates, etc.:  =3D3D
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for =
=3D
=3D3D
FILE SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your =
=3D
=3D3D
email messages, read these instructions that explain why and how to fix
=3D3D
it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
*************************************************************
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, visit the group's homepage and use the
dropdown menu at the top.  This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister
group.  This group will not allow for posting of emails, but will allow
you to join and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
FILE SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your
email messages, read these instructions that explain why and how to fix
it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
*************************************************************
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, visit the group's homepage and use the =
dropdown menu at the top.  This will allow you to unsubscribe your email =
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister =
group.  This group will not allow for posting of emails, but will allow =
you to join and share problem files, templates, etc.:  =
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for =
FILE SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your =
email messages, read these instructions that explain why and how to fix =
it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
*************************************************************
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, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
 This group will not allow for posting of emails, but will allow you to join 
and share problem files, templates, etc.:  
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

Other related posts: