I need to count data based on 3 criteria.. I have this working for 2
but now need a third..
ColA is just an identifier, ColB is the date some work was done, ColC
is if it was completed.

The results section is the count area. ColAA is the month/year to
check, ColBB is the count of how many dates from range ColBB match
Col4 (i.e. 5 dates fall in October 08). The function is the following
for that count:
=SUMPRODUCT(--(DATE(YEAR($B$1:$B$19),MONTH($B$1:$B$19),1)=AA1)).

This works without any problem.
I now need to do the same with the data in ColC. For example ColCC
should have 3 for October (5 started in October but only 3 completed
as marked by the letter P) take data from ColB check against ColAA if
true then check ColC if P then count ...



This is my data
ColA ColB ColC
A1 10/31/08 P
A2 09/07/08
A3 10/31/08
A4 10/31/08 P
A5 11/19/08 P
A6 10/16/08 P
A7 09/05/08 P
A8 12/09/08 P
A9 12/09/08 P
A10 12/09/08 P
A11 12/09/08 P
A42 12/09/08 P
A43 07/01/08 P
A44 12/02/08
A45 08/02/08
A46 12/09/08 P
A47 11/28/08 P
A48 11/13/08 P
A49 10/31/08 P

Results:
ColAA ColBB ColCC
Month Done in month: P Result
7-08 1 XX
8-08 1 XX
9-08 2 XX
10-08 5 XX
11-08 3 XX
12-08 7 XX

1. there are 4 "P"s in OCt. 2008

2. one way:

=SUM(IF((YEAR($B$1:$B$19)=YEAR(AA1))*(MONTH($B$1:$ B$19)=MONTH(AA1))*($C
$1:$C$19="P"),1,))

array-enter this formula i.e. CTRL+SHIFT+ENTER, as this is an array
formula

then copy down


On 11 Gru, 19:54, Chris Salcedo wrote:
I need to count data based on 3 criteria.. I have this working for 2
but now need a third..
ColA is just an identifier, ColB is the date some work was done, ColC
is if it was completed.

The results section is the count area. ColAA is the month/year to
check, ColBB is the count of how many dates from range ColBB match
Col4 (i.e. 5 dates fall in October 08). The function is the following
for that count:
=SUMPRODUCT(--(DATE(YEAR($B$1:$B$19),MONTH($B$1:$B$19),1)=AA1)).

This works without any problem.
I now need to do the same with the data in ColC. For example ColCC
should have 3 for October (5 started in October but only 3 completed
as marked by the letter P) take data from ColB check against ColAA if
true then check ColC if P then count ...

This is my data
ColA * *ColB * * *ColC
A1 * * *10/31/08 * * * *P
A2 * * *09/07/08
A3 * * *10/31/08
A4 * * *10/31/08 * * * *P
A5 * * *11/19/08 * * * *P
A6 * * *10/16/08 * * * *P
A7 * * *09/05/08 * * * *P
A8 * * *12/09/08 * * * *P
A9 * * *12/09/08 * * * *P
A10 * * 12/09/08 * * * *P
A11 * * 12/09/08 * * * *P
A42 * * 12/09/08 * * * *P
A43 * * 07/01/08 * * * *P
A44 * * 12/02/08
A45 * * 08/02/08
A46 * * 12/09/08 * * * *P
A47 * * 11/28/08 * * * *P
A48 * * 11/13/08 * * * *P
A49 * * 10/31/08 * * * *P

Results:
ColAA * * * * *ColBB * * * * * * * * *ColCC
Month * *Done in month: P Result
7-08 * * * * * * * 1 * * * * * * * * * * * XX
8-08 * * * * * * * 1 * * * * * * * * * * * XX
9-08 * * * * * * * 2 * * * * * * * * * * * XX
10-08 * * * * * 5 * * * * * * * * * * * * *XX
11-08 * * * * * 3 * * * * * * * * * * * * *XX
12-08 * * * * * 7 * * * * * * * * * * * * *XX

On Dec 11, 11:18*am, Jarek Kujawa wrote:
1. there are 4 "P"s in OCt. 2008

2. one way:

=SUM(IF((YEAR($B$1:$B$19)=YEAR(AA1))*(MONTH($B$1:$ B$19)=MONTH(AA1))*($C
$1:$C$19="P"),1,))

array-enter this formula i.e. CTRL+SHIFT+ENTER, as this is an array
formula

then copy down

On 11 Gru, 19:54, Chris *Salcedo wrote:

I need to count data based on 3 criteria.. I have this working for 2
but now need a third..
ColA is just an identifier, ColB is the date some work was done, ColC
is if it was completed.

The results section is the count area. ColAA is the month/year to
check, ColBB is the count of how many dates from range ColBB match
Col4 (i.e. 5 dates fall in October 08). The function is the following
for that count:
=SUMPRODUCT(--(DATE(YEAR($B$1:$B$19),MONTH($B$1:$B$19),1)=AA1)).

This works without any problem.
I now need to do the same with the data in ColC. For example ColCC
should have 3 for October (5 started in October but only 3 completed
as marked by the letter P) take data from ColB check against ColAA if
true then check ColC if P then count ...

This is my data
ColA * *ColB * * *ColC
A1 * * *10/31/08 * * * *P
A2 * * *09/07/08
A3 * * *10/31/08
A4 * * *10/31/08 * * * *P
A5 * * *11/19/08 * * * *P
A6 * * *10/16/08 * * * *P
A7 * * *09/05/08 * * * *P
A8 * * *12/09/08 * * * *P
A9 * * *12/09/08 * * * *P
A10 * * 12/09/08 * * * *P
A11 * * 12/09/08 * * * *P
A42 * * 12/09/08 * * * *P
A43 * * 07/01/08 * * * *P
A44 * * 12/02/08
A45 * * 08/02/08
A46 * * 12/09/08 * * * *P
A47 * * 11/28/08 * * * *P
A48 * * 11/13/08 * * * *P
A49 * * 10/31/08 * * * *P

Results:
ColAA * * * * *ColBB * * * * * * * * *ColCC
Month * *Done in month: P Result
7-08 * * * * * * * 1 * * * * * * * * * * * XX
8-08 * * * * * * * 1 * * * * * * * * * * * XX
9-08 * * * * * * * 2 * * * * * * * * * * * XX
10-08 * * * * * 5 * * * * * * * * * * * * *XX
11-08 * * * * * 3 * * * * * * * * * * * * *XX
12-08 * * * * * 7 * * * * * * * * * * * * *XX

Fantastic... Works great.. I assume that the logic is if year match
=true (=1) * if month match =true (=1) * cell ="P" =true=1 =false=0
soooo 1*1*1=1 if all criteria is true or 0 if not then sum...
Briliant.....
Thanks

exactly...
pls click YES if my post was helpful
;-)))

On 11 Gru, 22:55, Chris Salcedo wrote:
On Dec 11, 11:18*am, Jarek Kujawa wrote:





1. there are 4 "P"s in OCt. 2008

2. one way:

=SUM(IF((YEAR($B$1:$B$19)=YEAR(AA1))*(MONTH($B$1:$ B$19)=MONTH(AA1))*($C
$1:$C$19="P"),1,))

array-enter this formula i.e. CTRL+SHIFT+ENTER, as this is an array
formula

then copy down

On 11 Gru, 19:54, Chris *Salcedo wrote:

I need to count data based on 3 criteria.. I have this working for 2
but now need a third..
ColA is just an identifier, ColB is the date some work was done, ColC
is if it was completed.

The results section is the count area. ColAA is the month/year to
check, ColBB is the count of how many dates from range ColBB match
Col4 (i.e. 5 dates fall in October 08). The function is the following
for that count:
=SUMPRODUCT(--(DATE(YEAR($B$1:$B$19),MONTH($B$1:$B$19),1)=AA1)).

This works without any problem.
I now need to do the same with the data in ColC. For example ColCC
should have 3 for October (5 started in October but only 3 completed
as marked by the letter P) take data from ColB check against ColAA if
true then check ColC if P then count ...

This is my data
ColA * *ColB * * *ColC
A1 * * *10/31/08 * * * *P
A2 * * *09/07/08
A3 * * *10/31/08
A4 * * *10/31/08 * * * *P
A5 * * *11/19/08 * * * *P
A6 * * *10/16/08 * * * *P
A7 * * *09/05/08 * * * *P
A8 * * *12/09/08 * * * *P
A9 * * *12/09/08 * * * *P
A10 * * 12/09/08 * * * *P
A11 * * 12/09/08 * * * *P
A42 * * 12/09/08 * * * *P
A43 * * 07/01/08 * * * *P
A44 * * 12/02/08
A45 * * 08/02/08
A46 * * 12/09/08 * * * *P
A47 * * 11/28/08 * * * *P
A48 * * 11/13/08 * * * *P
A49 * * 10/31/08 * * * *P

Results:
ColAA * * * * *ColBB * * * * * * * * *ColCC
Month * *Done in month: P Result
7-08 * * * * * * * 1 * * * * * * * * * * * XX
8-08 * * * * * * * 1 * * * * * * * * * * * XX
9-08 * * * * * * * 2 * * * * * * * * * * * XX
10-08 * * * * * 5 * * * * * * * * * * * * *XX
11-08 * * * * * 3 * * * * * * * * * * * * *XX
12-08 * * * * * 7 * * * * * * * * * * * * *XX

Fantastic... Works great.. I assume that the logic is if year match
=true (=1) ** if month match =true (=1) * cell ="P" =true=1 =false=0
soooo 1*1*1=1 if all criteria is true *or 0 if not then sum...
Briliant.....
Thanks- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -