CODEWITHSUNDEEP
google-sheets
NuMs
NuMs

Reputation: 149

COUNTIF across multiple sheets with dupilcate detection

Was hard to give this question an accurate title, so sorry if its wrong.

Anyway, given the following data...

Example1

With each sheet being a different player I need to count the number of games played in each colored room across all players.

That's easy enough with countif..

Example2

But some games have multiple players and in those cases the game would be counted multiple times using the above method.

One solution I'm trying giving each game with multiple players an ID, a random string, then checking if an ID is present and if so, only count all games with the same ID as one.

The end result should end up like this.

Example3

Despite my effort I havent been able to figure this out. Anyone have any tips on how to go about this? or if there is a better way to prevent the same game from being counted twice.

If possible I'd like to avoid using the apps-script. Thanks.

Upvotes: 0

Views: 325

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18707

Me answer is based on guess:

  1. count each item with no ID
  2. count items with same Id's as one item

Then you may go to Result_sheet and use the formula:

=QUERY({QUERY(UNIQUE(QUERY({Sheet1!A:B;Sheet2!A:B;Sheet3!A:B},"select Col1, Col2 where Col2 <> ''")),"select Col1, 1");QUERY({Sheet1!A:B;Sheet2!A:B;Sheet3!A:B},"select Col1, 1 where Col1 <> '' and Col2 = ''")},"select Col1, sum(Col2) where Col1 <> '' group by Col1 label sum(Col2) '', Col1 '  Game Count'")

If you add sample file, I'll make an example with this formula.

Upvotes: 1

Related Questions