Display count of events

[CT-Gene]

Excel Pros

I am Looking for some help on an Excel problem

The goal is to have the key field list the number of events happening for the department on the same day. In this example we have 13 events happening for 3 different departments on the same day. I am looking for a formula that will count the number of events per department per day and put the incremented value in the correct cell.

      A      B      C
---------|-------|------
 1|  Key   Date    Dept
 2|   1  01/12/09  CO
 3|   1  01/12/09  IS
 4|   2  01/12/09  CO
 5|   1  01/12/09  IT
 6|   2  01/12/09  IT
 7|   3  01/12/09  CO
 8|   4  01/12/09  CO
 9|   2  01/12/09  IS
10|   3  01/12/09  IS
11|   4  01/12/09  IS
12|   3  01/12/09  IT
13|   5  01/12/09  CO
14|   4  01/12/09  IT

I am then using the Key value to place the tasks (not shown) on the correct day showing the first 10 events that occur on that date

[broro183]

hi
What is the "correct cell"?
Can you please upload a sample file to provide more context & allow us to help you more effectively?

Rob

[DonkeyOte]

If I've understood...

If you're running XL2007 you can make use of COUNTIFS such that:

A2: =COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2)
copy down

If you're not running XL2007 you need an alternative approach based on the fact that COUNTIFS does not exist, you have only COUNTIF which caters for just the 1 test (you have 2 - date & dept) ... you then have 2 options

a) Sumproduct / Array

A2: =SUMPRODUCT(--($B$2:$B2=$B2),--($C$2:$C2=$C2))
copy down

b) create Concatenation and use COUNTIF - FAR more efficient than option A

D2: =$B2&":"&$C2
copy down

A2: =COUNTIF($D$2:$D2,$B2&":"&$C2)
copy down

If you're not running XL2007 I would strongly advise option B