# 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 ComputersThread 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 ComputersThread Starter

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

3. ### 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 ComputersThread Starter

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

You are an absolute star! Thank you so much!

xx

5. ### 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.

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.

over 733,556 other people just like you!

Thread Status:
Not open for further replies.

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