Help with Excel - Multiple Criteria

[FeedbackPlus]

Hi

I am looking to count the amount of entries of peoples names in one column matched against a week ending date in another column so each week it will automatically count how many times 'Joe Bloggs' etc is entered.

I have the names in column E and the Week Ending dates in column B.

Any ideas?

[PeteSmith]

COUNTIFS formula will work:

=COUNTIFS(B2:B7,"=12/4/9",E2:E7,"Joe Bloggs")

Welcome to the forums.

[FeedbackPlus]

Hi thanks for your reply.

I tried it using the countifs formula you showed me but it doesnt work. It comes up with #Name? error.

[PeteSmith]

The COUNTIFS formula is new, and will only work on Excel 2007. If you use a earlier version you will need to use SUM within an array. Like so:

=SUM(($B$2:$B$7=VALUE("7/4/2009"))*($E$2:$E$7="Joe Bloggs"))

To input as an array you must use CTRL+SHIFT+ENTER when entering the formula.

[Lucio]

Personally I use trickery for things like this, and simply grab another column and put in it =B2&"X"&E2

You can then lay out in a new spreadsheet an array with names along the top and dates down the row and use =COUNTIF(ROW&"X"&COLUMN,0)

[FeedbackPlus]

Hello chaps,

Thank you for the help, i have put the codes in that you gave me and they work a treat. This forum is great!

Rocky