i have few columns, but want to do calculation of one of them, based on another, for example:

Column B is Departmentfrom B2 to B300)

trs

trs

too

too

klo

klo

pko

pko

na

na...

While Column I is ranking of those (from I2 to I300):

High

high

high

low

low

moderate

critical

na

not applicable

good

moderate

critical

critical...

so, i'm trying to find a formula, that would look for example for all 'trs' Department, but with Critical ranking, then moderate, and so on... for each department:

i tried few formulas, but no luck:

=COUNTIFS(B2:B300, ">"&0, I2:I300,"<"&B2)

=SUMPRODUCT(--ISNUMBER(B$2:B$300),--(I$2:I$300="Critical"))

=COUNTIFS(B2:B300, "Critical", I2:I300, "<>0")

they all return Zero!!! I checked thje formatt of the cell, and it's on General, but still no luck....