ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Countif or sumif with 2 criteria (https://www.excelbanter.com/excel-discussion-misc-queries/149739-countif-sumif-2-criteria.html)

chrisk
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


Mike H
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


Toppers
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


Toppers
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


chrisk
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


chrisk
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


Toppers
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


chrisk
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

Toppers
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

chrisk
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

Toppers
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


All times are GMT +1. The time now is 12:40 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com