1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Formulas: Multiple Criteria?

Discussion in 'Business Applications' started by Hate Computers, Feb 16, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Hate Computers

    Hate Computers Thread Starter

    Joined:
    Jan 4, 2007
    Messages:
    91
    Hi everyone,

    I have a spreadsheet which I use for work and I need help trying to suss out a formula.

    The spreadsheet lists each client, the member of staff assigned to that client, and the date the client's contract is due to expire. I need to know how many clients each member of staff has at any one time.

    For example, say column A is the staff name and column B is the client's expiry date, how do I calculate how many clients are still current, i.e. as of today.

    E.G.

    A B
    Margaret 02/02/2006
    Julie 08/03/2007
    Margaret 15/06/2006
    Margaret 20/10/2007
    Julie 27/06/2007


    Using the above as an example, I need a formula that shows me at-a-glance that Margaret currently has only 1 client, Julie has 2 clients and so on...

    I really hope this makes sense to someone! It's quite hard to explain I guess! I have trawled through loads of websites trying to find out but can't seem to find anything.

    Thanks in advance!

    Sarah
     
  2. Hate Computers

    Hate Computers Thread Starter

    Joined:
    Jan 4, 2007
    Messages:
    91
    Oops. The example columns aren't meant to be squashed up! Hope it still makes sense!

    :eek:
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    When I copy your sample data to a worksheet it "occupies" A1:B5. So:

    1. Enter Margaret in D1

    2. Enter Julie in D2

    3. Enter this formula in E1:

    =SUMPRODUCT(($A$1:$A$5=D1)*($B$1:$B$5>TODAY()))

    and copy it to E2.

    HTH
     
  4. Hate Computers

    Hate Computers Thread Starter

    Joined:
    Jan 4, 2007
    Messages:
    91
    It worked!!

    You are an absolute star! Thank you so much!

    xx
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I think Excel 2007 has an enhanced SUMIF, i.e. multiple criteria, but I doubt you have 07 hence the SUMPRODUCT solution. You might want to use >=TODAY actually.

    Anyway, you're welcome. Use "Thread Tools" to "Mark Solved" if you wish. :)
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/544619

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice