COUNTIF capability

[Guest]

Hi all,

 

[Bob Phillips]

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

--
HTH

Bob

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

 

[Guest]

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

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")*(D310="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")*(D310="Closed"))

Adapt the ranges to suit ..

---

 

[Naraine Ramkirath]

use the filters

Hi all,