software

I have a spreadsheet used to track claims. Case Managers are assigned the
cases based on the surname of the client. For instance, Jane case manages
all clients with a surname between A and K and Jill manages all clients with
a surname between L and Z.

I would like to set up a formula to count the number of cases each case
manager has. Can this be done with a wildcard?

I have been fighting with this trying to find the answer, which I'm sure is
simple. Any assistance you can provide is greatly appreciated.

Leslie,

For A-K use
=COUNTIF(A1:A10,quot;lt;kquot;)
For L-Z, use
=COUNTIF(A1:A10,quot;lt;=zquot;)-COUNTIF(A1:A10,quot;lt;=lquot;)
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
quot;Lesliequot; gt; wrote in message
...
gt;I have a spreadsheet used to track claims. Case Managers are
gt;assigned the
gt; cases based on the surname of the client. For instance, Jane
gt; case manages
gt; all clients with a surname between A and K and Jill manages all
gt; clients with
gt; a surname between L and Z.
gt;
gt; I would like to set up a formula to count the number of cases
gt; each case
gt; manager has. Can this be done with a wildcard?
gt;
gt; I have been fighting with this trying to find the answer, which
gt; I'm sure is
gt; simple. Any assistance you can provide is greatly appreciated.
=SUMPRODUCT(--(UPPER(LEFT($A$2:$A$200,1))gt;=quot;Aquot;),--(UPPER(LEFT($A$2:$A$200,1)
)lt;=quot;Jquot;))

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Lesliequot; gt; wrote in message
...
gt; I have a spreadsheet used to track claims. Case Managers are assigned the
gt; cases based on the surname of the client. For instance, Jane case manages
gt; all clients with a surname between A and K and Jill manages all clients
with
gt; a surname between L and Z.
gt;
gt; I would like to set up a formula to count the number of cases each case
gt; manager has. Can this be done with a wildcard?
gt;
gt; I have been fighting with this trying to find the answer, which I'm sure
is
gt; simple. Any assistance you can provide is greatly appreciated.
Thank you Chip - you saved me a lot of time!

quot;Chip Pearsonquot; wrote:

gt; Leslie,
gt;
gt; For A-K use
gt; =COUNTIF(A1:A10,quot;lt;kquot;)
gt; For L-Z, use
gt; =COUNTIF(A1:A10,quot;lt;=zquot;)-COUNTIF(A1:A10,quot;lt;=lquot;)
gt;
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt;
gt; quot;Lesliequot; gt; wrote in message
gt; ...
gt; gt;I have a spreadsheet used to track claims. Case Managers are
gt; gt;assigned the
gt; gt; cases based on the surname of the client. For instance, Jane
gt; gt; case manages
gt; gt; all clients with a surname between A and K and Jill manages all
gt; gt; clients with
gt; gt; a surname between L and Z.
gt; gt;
gt; gt; I would like to set up a formula to count the number of cases
gt; gt; each case
gt; gt; manager has. Can this be done with a wildcard?
gt; gt;
gt; gt; I have been fighting with this trying to find the answer, which
gt; gt; I'm sure is
gt; gt; simple. Any assistance you can provide is greatly appreciated.
gt;
gt;
gt;