CODEWITHSUNDEEP
excel
jonooo
jonooo

Reputation: 65

Excel - How to count rows with a criteria but only once for each ID

In a database, two things are getting logged which is the client id and a status type id - this is getting logged every time a player in-game (who has a client id) has a change in their "ClassTypeID". How would I count the number of times a certain ClassTypeID appears, but only count this once for every client id?

For example if a player with client id 1 changes classes, then changes back, the logs will look something like this:

ID -- ClassTypeID

1 ---- 1

1 ---- 2

1 ---- 1

Upvotes: 0

Views: 85

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You would use something like this array formula:

=SUM(IF($A$2:$A$8=E2,1/COUNTIFS($B$2:$B$8,$B$2:$B$8,$A$2:$A$8,E2)))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly the Excel will put {} around the formula.

enter image description here

Upvotes: 2

Related Questions