COUNTIF capability

  • Thread starter Thread starter Guest
  • Start date Start date
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)
 
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 ..

---
 
Back
Top