Can I use 2 countif formulas?

Microsoft Excel 2003 (full product)
December 16, 2010 at 20:53:38
Specs: Windows XP
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 Crushing

29/11/2010 Crushing = 2
30/11/2010 Workshop = 2
30/11/2010 Crushing = 2

I have 6 different departments to capture and was wondering how to do this.


See More: Can I use 2 countif formulas?

Report •


#1
December 17, 2010 at 06:53:02
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.


Report •

#2
December 17, 2010 at 07:42:08
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

http://www.skeptic.com/


Report •

#3
December 17, 2010 at 16:07:44
Thank you so much!! The first formula works a treat and is exactly what I was looking for :)

Report •
Related Solutions


Ask Question