Complex count if

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.

Hi Sandro,

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

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

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

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

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.

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

Tell me if I can help more.

1 Like

Thank you so much again!


Have a nice weekend yourself :smile:

1 Like