Counting of items with two conditions in two areas

[Dane123]

Hello,

I want to find the maximum amount of the same number for the same ID in a dataset of many different IDs.

I will attach an Excel File that encompasses my question.

Thank you!

Best,
Daniel

[Murtadau_Gabier]

Hi Dane,

You can enter the following into cell D2.

=MAX(IF(A:A=A2,B:B))

please note that you need to submit this formula by hitting CNTRL+SHIFT+ENTER

You can then drag the formula down.

If this helps please hit the Star at the bottom

regards,
Murtadau

[modytrane]

can you enter expected result for some of the entries?
What do you mean by Max of same SITEID for Player 1? You have this repeated three times. Are you expecting three different results?

[José Augusto]

Try this formula in C2 and copy down

Please Login or Register  to view this content.

[Dane123]

No I repeated it 3 times because the 3 first lines are for player 1. It should be the same for Player 1 then.

[Dane123]

Murtadau: Your function gave me the SITEID of the site with the most mentions for each player seperately. But I don't want to know the ID itself but the number of times it appears for each Player. So Player 1 would have a 1 as all three IDs only appear once but for Player 2 it should be 2, as 2 and 3 appear twice.

[Murtadau_Gabier]

Hi Dane123,

I think a simple COUNTIFS will do the trick here then.

Use the following in C2 and drag down.

=COUNTIFS($A:$A,$A2,$B:$B,$B2)

regards,
Murtadau

[Dane123]

Well, that was easy! Thank you!

[Murtadau_Gabier]

Your welcome. Glad I could help.