/docs/community?hl=en
This content is likely not relevant anymore. Try searching or browse recent questions.
-
10/14/19
Countuniqueifs on multiple non-connected columns? 1 Recommended Answer 4 Replies 22 Upvotes
1 Recommended Answer
$0 Recommended Answers
I'm trying to use google sheets to handle the sorting and collating of results in a chess tournament; as it is, I've got a column for who's playing white, a column for who's playing black, and two for results (though one would probably do, that's not relevant here). All is going fine, until I realise that I want to add bonus points for individual opponents played, in this case half a point per individual opponent. So, I'm using countuniqueifs, but there's a problem - the range in that only counts one column, whilst I've got two columns of games. I could bodge things; one possible solution is to get another table, pulling the games twice, so A v B would be listed as A v B and B v A and a countuniqueifs would go over the first column only (downside of taking a decent chunk of work and not easily expanding as more games are played). The other solution I can think of is to not list who's black and who's white, just putting the player name and opponent in, say, alphabetical order, so I can have one countuniqueifs per white column and one per black column (which doesn't work now due to that counting AvB and BvA as two different opponents); downside here is flexibility for future use - if, for example, I decide we need tiebreaks at all, I can't pull out number of games as white and number as black under this system. I've tried countuniqueifs(Results!A2:B, Results!B2:A, A2), but you can't have backwards ranges (so going over B2:B, then A2:A), it seems. countuniqueifs(Results!A2:A&Results!B2:B, Results!B2:B&ResultsA2:A, A2) throws an error about needing a range not an array, and it doesn't seem to help throwing in arrayformula() either around everything, or around the concatenations. =ARRAYFORMULA(countuniqueifs(Results!$A$2:$B, Results!B2:B&Results!A2:A, A2)/2) is my best try, but there's an error that the two ranges are different sizes, though they're not (presumably due to the whole range bodge).

Is there an elegant solution? Feels like there should be, but I'm missing something here. Maybe some regex would work, but I've never touched the stuff.
All Replies (4)
10/15/19
Hi Matthew:
It will help if you share your Google spreadsheet with some sample but realistic data
and show us your expected result

Cheers
Yogi Anand
marked this as an answer
Helpful?
-
10/15/19
Hi Yogi,

https://docs.google.com/spreadsheets/d/1QmtXNDFYQhVntHP9ZrD8snjukSneP9m8HbX9Ov-mPoM/edit?usp=sharing

So, we've got four players, and Player1 has played two opponents, Player2 has played one opponent, Player3 has played two, and Player4 one. So Table!D2 should be 1, Table!D3 0.5, Table!D4 1, and Table!D5 should be 0.5 (as they get half a point per unique opponent). D2 shows my last tried formula, and D3-D6 show my initial attempt, which double counts some games.

Thanks,

Matthew.
marked this as an answer
Helpful?
-
10/16/19
Thanks, Matt. That looks like it works well, after some quick testing.
marked this as an answer
Helpful?
This question is locked and replying has been disabled.
Discard post? You will lose what you have written so far.
Write a reply
10 characters required
Failed to attach file, click here to try again.
Discard post?
You will lose what you have written so far.
Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

A problem occurred. Please try again.
Create Reply
Edit Reply
Delete post?
This will remove the reply from the Answers section.
Notifications are off
Your notifications are currently off and you won't receive subscription updates. To turn them on, go to Notifications preferences on your Profile page.
Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
Report post
Questions about a different Google product are no longer considered off-topic and should be moved to the relevant community.
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
This reply is no longer available.
/docs/threads
//accounts.google.com/ServiceLogin
You'll receive email notifications for new posts at
Unable to delete question.
Unable to update vote.
Unable to update subscription.
You have been unsubscribed
Deleted
Unable to delete reply.
Removed from Answers
Marked as Recommended Answer
Removed recommendation
Undo
Unable to update reply.
Unable to update vote.
Thank you. Your response was recorded.
Unable to undo vote.
Thank you. This reply will now display in the answers section.
Link copied
Locked
Unlocked
Unable to lock
Unable to unlock
Pinned
Unpinned
Unable to pin
Unable to unpin
Marked
Unmarked
Unable to mark
Reported as off topic
/docs/profile/0?hl=en