COUNTIF capability

B

Bob Phillips

a) =SUMPRODUCT(--(C2:C20="DJC"),--(B2:B20=1))
b) =SUMPRODUCT(--(C2:C20="DJC"),--(B2:B20=1),--(D2:D20="Closed"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
M

Max

One way using SUMPRODUCT ..
(a) the amount of tasks i allocated to DJC in January
(b) the amount of tasks i allocated to DJC in January that are closed
Click to expand...

Via reading the month #s returned in col B

(a):
=SUMPRODUCT((B3:B10=1)*(C3:C10="DJC"))

(b):
=SUMPRODUCT((B3:B10=1)*(C3:C10="DJC")*(D3:D10="Closed"))

Perhaps more robust by specifying it as "month & year" & reading this
criteria from the dates in col A:

(a):
=SUMPRODUCT((TEXT(A3:A10,"mmmyy")="Jan07")*(C3:C10="DJC"))

(b):
=SUMPRODUCT((TEXT(A3:A10,"mmmyy")="Jan07")*(C3:C10="DJC")*(D3:D10="Closed"))

Adapt the ranges to suit ..
 
Top