count on 3 criteria

[Chris Salcedo]

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

 

[Jarek Kujawa]

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

 

[Chris Salcedo]

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

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

 

[Jarek Kujawa]

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