SOLVED

Count with 2 criteria in one range per row

Copper Contributor

Greetings!

 

Specification: Excel 2016, Win10.

 

Context: person has # X and # Y. There are multiple persons with different XY's. It is possible for the numbers to be exactly the same in XY. 

 

Questions:

What function and criteria to use to count how many persons have identical specific XY numbers?

Is it possible to calculate all this with count functions, if's, and's? Or at least keep it in the lines of count functions. 

 

Example:

IMG_20230610_211108.jpg

Bill and Kelly have the same X 32 and Y 56.

Criteria in this case is that X = 32 and Y = 56. (Both have to be true.) 

That is why expected result = 2.

If Kelly had a Y of 23, expected result would be 1, as criteria Y = 56 wouldn't be true.

So, the result is how many persons X = 32 and Y = 56. Names are not required.

 

I do manage to count with help of a few more cells, but I want to do it in just one cell, as it is more eye appealing and gets very long if it's a long database.

3 Replies
best response confirmed by AB_15 (Copper Contributor)
Solution

@AB_15 

=COUNTIFS($B$2:$B$6,32,$C$2:$C$6,56)

You can try COUNTIFS.

countifs.JPG

@AB_15 

Or if you want to check the complete table use a pivot table.

X and Y in rows area, Name in values area.

 

@AB_15 Give a try to-

=SUM(--(COUNTIFS(B2:B6,B2:B6,C2:C6,C2:C6)>1))

 Harun24HR_0-1686462513702.png

 

1 best response

Accepted Solutions
best response confirmed by AB_15 (Copper Contributor)
Solution

@AB_15 

=COUNTIFS($B$2:$B$6,32,$C$2:$C$6,56)

You can try COUNTIFS.

countifs.JPG

View solution in original post