Countif or sumif with 2 criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I count the number of items per day per person?
Did a SUMIF but it counts all done on that day by all people.
I want to count all done on that day by each person.
Column A Date
Column B person number eg 0706/05/
Column C no of items

Date (number) Person (text) Items Answer
25/01/2007 0701/38/12 3
25/01/2007 0701/38/13 7
25/01/2007 0701/38/14 5 15
25/01/2007 0701/15/22 6
25/01/2007 0701/15/23 7
25/01/2007 0701/15/27 6 19
25/01/2007 0701/50/18 9 9

I'm looking for the 15 19 & 9 to appear in the Answer column
Thanks
 
Try:-

=SUMPRODUCT((A1:A10=D1)*(B1:B10=D2)*(C1:C10))

where:-

D1 = the date you want
D2 = persons number
 
try:


put in D2 and copy down:

=IF(LEFT(B2,7)=LEFT(B3,7),"",SUMPRODUCT(--(LEFT($B$2:$B$1000,7)=LEFT(B2,7)),$C$2:$C$1000))

HTH
 
for a given date ....

=IF(LEFT($B2,7)=LEFT($B3,7),"",SUMPRODUCT(--($A$2:$A$1000=DATE(2007,1,25)),--(LEFT($B$2:$B$1000,7)=LEFT($B2,7)),$C$2:$C$1000))
 
This gives me the same numbers as are in col c already. Have I missed
something? Don't really understand SUMPRODUCT
 
try:

=IF(LEFT($B2,7)=LEFT($B3,7),"",SUMPRODUCT(--($A$2:$A$100=A2),--(LEFT($B$2:$B$100,7)=LEFT($B2,7)),$C$2:$C$100))

Assumption: data is sorted date and person mumber within date
 
So close...but no biscuit...Sorry I'm brain dead.
I sort of get what the formula is doing, but that's it.

Here is part of the actual data I'm trying to sort

25/01/2007 0701/38/12 4
25/01/2007 0701/38/13 6
25/01/2007 0701/38/14 8 person 38 has done 18
27/01/2007 0701/15/22 8
27/01/2007 0701/15/23 6 person 15 has done 14
31/01/2007 0701/15/26 4
31/01/2007 0701/15/27 10 person 15 has done 14
10/01/2007 0701/50/18 10 person 50 has done 16
12/01/2007 0701/47/15 6
12/01/2007 0701/47/16 10 person 47 has done 16
11/01/2007 0701/04/06 8
11/01/2007 0701/04/07 4
11/01/2007 0701/04/08 4
11/01/2007 0701/04/09 3 person 04 has done 19
13/01/2007 0701/04/12 12
13/01/2007 0701/04/14 4 person 04 has done 16
17/01/2007 0701/04/20 16 person 04 has done 16
20/01/2007 0701/04/24 2
20/01/2007 0701/04/25 2
20/01/2007 0701/04/26 6
20/01/2007 0701/04/27 4
20/01/2007 0701/04/28 6 person 04 has done 16
24/01/2007 0701/04/33 4 and so on....
24/01/2007 0701/04/34 4
24/01/2007 0701/04/35 6
26/01/2007 0701/04/39 6
26/01/2007 0701/04/40 8
29/01/2007 0701/37/28 4
29/01/2007 0701/37/29 6
29/01/2007 0701/37/30 4
19/01/2007 0701/48/05 6
19/01/2007 0701/48/06 9
27/01/2007 0701/48/15 6
27/01/2007 0701/48/16 4
27/01/2007 0701/48/17 5
31/01/2007 0701/48/22 6
31/01/2007 0701/48/23 7
31/01/2007 0701/48/24 4


Thanks
 
=IF(AND($A2=$A3,LEFT($B2,7)=LEFT($B3,7)),"",SUMPRODUCT(--($A$2:$A$100=A2),--(LEFT($B$2:$B$100,7)=LEFT($B2,7)),$C$2:$C$100))

P.S. I think some of the totals in your previous respponse are wrong.


20/01/2007 0701/04/24 2
20/01/2007 0701/04/25 2
20/01/2007 0701/04/26 6
20/01/2007 0701/04/27 4
20/01/2007 0701/04/28 6 person 04 has done 16 ... 20??
 
Oh my...it works...speechless. Thanks very much.
Yes, I can't add up that's why I use spread sheets!
Its warm and humid and I'm falling asleep.
IF(AND($A2=$A3,LEFT($B2,7)=LEFT($B3,7)),"", I get this bit

=SUMPRODUCT(--($A$2:$A$100=A2),--(LEFT($B$2:$B$100,7)=LEFT$B2,7)),$C$2:$C$100))
I don't get this bit. What's the '--' bit for?
 
Back
Top