Counting based on another cell reference

[siirma]

Hi there,

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....

 

[pplstuff]

You need to make a table, With all Depts on the left and all the rankings at the top. Then, enter this formula… (be sure to remove duplicates from both lists)

=COUNTIFS(DataSheet!$B:$B,$A2,DataSheet!$I:$I,B$1)

 

[siirma]

but, that's the problem, i have duplicates and i cant remove them, since all those departments are later broken into different divisions, that's why there are dupplicates for example trs and so on, hence i would get duplicates in ratings too?!

i dont undersant what A2 and B1 reflects to?

 

[siirma]

Sorry, my error, misstyped the column number

thanks for the help - it worked

 

[siirma]

ADVERTISEMENT

but a quick q: sometimes the cell I3 might have different ratings (i,e, Moderate), why doesn't for example "Critical" works? Since i'd be looking for "Hig" too, but it might change along the way?
=COUNTIFS(DataSheet!$B:$B,$A2,DataSheet!$I:$I,"Critical")
thanks

 

[pplstuff]

Not sure I understand the question... If you're looking to count MULTIPLE criteria, you might want to assign a # to each rating so you can use < or > in your counts.

 

[siirma]

why this option oesnt work:
=COUNTIFS(B2:B300,"TRS", I2:I300,"Critical")
=COUNTIFS(B2:B300,"TRS", I2:I300,"High")
=COUNTIFS(B2:B300,"KLO", I2:I300,"Critical")
=COUNTIFS(B2:B300,"KLO", I2:I300,"High")
=COUNTIFS(B2:B300,"TOO", I2:I300,"Critical")
=COUNTIFS(B2:B300,"TOO", I2:I300,"High")...

and so on, since next time i enter the data, the trs in cell A2 might have LOW ratings, and KLO from cell A6 might have High rating, or even the order of department might change:

Department	Rating
trs	High
trs	high
too	high
too	low
klo	low
klo	moderate
pko	critical
pko	na
na	not applicable
na	good
trs	moderate
too	critical
klo	critical

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[siirma]

it worked, my comp needed the restart
so this worked:
=COUNTIFS(B2:B300,"TRS", I2:I300,"Critical")