I am using excel 2003. I have a whole register of one document submitted with the date it was completed and what department it was completed by. I need a formula that will calculate how many documents were done each day by what department. Below is an example:

Column A Column B

30/11/2010 Workshop

30/11/2010 Crushing

30/11/2010 Workshop

30/11/2010 Crushing

29/11/2010 Crushing

29/11/2010 Crushing29/11/2010 Crushing = 2

30/11/2010 Workshop = 2

30/11/2010 Crushing = 2I have 6 different departments to capture and was wondering how to do this.

Try SUMPRODUCT: =SUMPRODUCT(($A$1:$A$6 = "30/11/2010") * ($B$1:$B$6 = "Workshop"))

You can also use lists of unique values and cell references:

E F 1 30/11/2010 Workshop 2 Crushing=SUMPRODUCT(($A$1:$A$6 = E1) * ($B$1:$B$6 = F1))

=SUMPRODUCT(($A$1:$A$6 = E1) * ($B$1:$B$6 = F2))

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

~~Today must be the day for =COUNTIFS()~~If you have 2007, you can also try:

=COUNTIFS(A1:A6,"=30/11/2010",B1:B6,"=Workshop")

or

A B 1) 30/11/2010 Workshop 2) 30/11/2010 Crushing 3) 30/11/2010 Workshop 4) 30/11/2010 Crushing 5) 29/11/2010 Crushing 6) 29/11/2010 Crushing 7) 8) 29/11/2010 Crushing =COUNTIFS(A1:A6,A8,B1:B6,B8) 9) 30/11/2010 Workshop =COUNTIFS(A1:A6,A9,B1:B6,B9) 10) 30/11/2010 Crushing =COUNTIFS(A1:A6,A10,B1:B6,B10)Just noticed your using 2003, sorry....

MIKE

Thank you so much!! The first formula works a treat and is exactly what I was looking for :)

Ask Your Question

Weekly Poll