Adding the number of instances across two columns

**ffcburt** · 03-21-2013, 07:41 AM

First time poster, treat me gently.

Not the most knowledgeable when it comes to excel, treat me gently.

Other than that...

I have a spreadsheet that in one column shows the performance rating that someone has got (1, 2 or 3), and in the next column it shows the potential rating someone has got (1, 2, 3).

I am trying to count the instances of people who have a performance rating of 1 and a potential rating of 1. And each variant thereof (e.g. performance rating 2, potential rating 1, etc. etc.).

PERF POT
1 3
1 2
1 1
2 3
3 3

So for example, the formula I end up using should return a "1" for the number of instances where someone has a performance rating of 1 (in the PERF column), and a potential rating of 1 (in the POT column) - apologies for the formatting...

I have tried a variety of things and ended up getting error message after error message, so any advice would be gratefully receiced.

Many thanks.

Burt

**benishiryo** · 03-21-2013, 08:30 AM

hi Burt, welcome to the forum. we're mostly gentle people~ =)

assuming data in A2:B6, try:
=COUNTIFS($A$2:$A$6,1,$B$2:$B$6,1)

you could also have those in red referred to a cell reference instead, so that you can easily drag the formula down to calculate other combinations. for eg. 1 in D1 & 1 in E1:
=COUNTIFS($A$2:$A$6,D2,$B$2:$B$6,E2)

and if you have 1 in D2 & 2 in E2, the formula can just be copied down & calculated