| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif or sumif with 2 criteria
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif or sumif with 2 criteria
Try:-
=SUMPRODUCT((A1:A10=D1)*(B1:B10=D2)*(C1:C10)) whe- D1 = the date you want D2 = persons number "chrisk" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif or sumif with 2 criteria
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 "chrisk" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif or sumif with 2 criteria
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)) "Toppers" wrote: 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 "chrisk" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif or sumif with 2 criteria
This gives me the same numbers as are in col c already. Have I missed
something? Don't really understand SUMPRODUCT "Mike H" wrote: Try:- =SUMPRODUCT((A1:A10=D1)*(B1:B10=D2)*(C1:C10)) whe- D1 = the date you want D2 = persons number "chrisk" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif or sumif with 2 criteria
Close...it worked for the first 3 then the date changes and it went...odd...
"Toppers" wrote: 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 "chrisk" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif or sumif with 2 criteria
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 "chrisk" wrote: Close...it worked for the first 3 then the date changes and it went...odd... "Toppers" wrote: 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 "chrisk" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif or sumif with 2 criteria
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif or sumif with 2 criteria
=IF(AND($A2=$A3,LEFT($B2,7)=LEFT($B3,7)),"",SUMPRO DUCT(--($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?? "chrisk" wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif or sumif with 2 criteria
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? "Toppers" wrote: 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?? "chrisk" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif or sumif with 2 criteria
The -- converts TRUE/FALSE to 1/0 which allows SUMPRODUCT to do the necessary
maths. Look here for a more detailed explanation: http://www.xldynamic.com/source/xld.SUMPRODUCT.html "chrisk" wrote: 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? "Toppers" wrote: 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?? "chrisk" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif & Sumif with Multiple criteria | Excel Worksheet Functions | |||
| multiple criteria with countif or sumif | Excel Worksheet Functions | |||
| Multiple Criteria for COUNTIF and SUMIF | Excel Worksheet Functions | |||
| Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
| SUMIF/COUNTIF with an additional criteria | Excel Worksheet Functions | |||
Linear Mode
