Complex count if

Ask for Help
1

Hey guys
Appreciate any help with a formula.
I am looking to get the number of collectibles someone has that match a complete collection.
Players are the people playing - where the value should be displayed.
Collection is the amount of available collectibles and their owners. (there are multiple instances of the same item, but with a unique collectibleID)
FullCollection is what a full collection looks like.

My objective in the end is to use a progress bar to show how far the player is to get a full collection.

2

Hi Sandro,

It’s the “Completeness” column right? I see you have an ARRAYFORMULA there. Is it not working?

3

No, that was me trying to solve it but with no luck.

4

I don’t see any matching names from the Collection and FullCollection. Is there a problem there or am I missing something?

5

apologies, the data on the collection was old as I was using mock data

6

Hi Sandro, I think I have managed to make it work.

In the Collection sheet, I added a column to check if the collection in the row is in the FullCollection list or not.

image
image999Ă—386 67.9 KB

In the Players sheet, I used a combination of ARRAYFORMULA & QUERY to get what looks like what you need for your case.

image
image988Ă—206 31 KB

Tell me if I can help more.

1 Like
7

Thank you so much again!

2 Likes
8

Have a nice weekend yourself :smile:

1 Like