# Counting based on another cell reference

#### siirma

##### New Member
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....

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### pplstuff

##### Well-known Member
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

##### New Member
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

##### New Member
Sorry, my error, misstyped the column number

thanks for the help - it worked

#### siirma

##### New Member
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

##### Well-known Member
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

##### New Member
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

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

Replies
1
Views
328
Replies
2
Views
951
Replies
6
Views
930
Replies
0
Views
180
Replies
6
Views
335

1,195,964
Messages
6,012,592
Members
441,714
Latest member
mcgeesusana

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back