CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
LADD
LADD

Reputation: 95

How Do I CountIf With Array and Concatenate?

I'm looking to count the unique combinations of Name, Date and Location.

Right now the thought process is to concatenate the 3. Then use the Unique function to weed out any doubles. Then use the countif (contains Name "wildcard" Location). The issue is the array function doesn't seem to work to automatically update new entries.

The goal is to basically weed out any double entries of the same (Name, Date and Location) then count the number of entries left. I feel like I'm on the right track but there's probably 1 magic function I can use to do it all.

I've come up with this formula but it returns an error

=COUNTUNIQUE(ARRAYFORMULA(CONCAT(CONCAT(B2:B,D2:D),E2:E)),CONCATENATE(G3,"*",H1))

Here's the link to the project.

Can someone steer me in the right direction or logic of how this should work?

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

Upvotes: 1

Views: 910

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34370

You can use Unique on multiple columns so I would do something like this:

=rows(unique(filter({B2:B,D2:E},B2:B<>"")))

enter image description here

The unique rows from the three selected columns are shown on the right as a check.

EDIT

If you want to display the unique values in a table grid format, use a pivot query on the same data:

=query(unique(filter({B2:B,D2:E},B2:B<>"")),"select Col1,count(Col2) group by Col1 Pivot Col3")

enter image description here

Upvotes: 3

LADD
LADD

Reputation: 95

enter image description here

The only issue is i still need to be able to count the unique entries in a table grid format. To fo that i need to concatenate the strings and individually check if they contain the player and location. I can't use array formula because there are 3 strings instead of only 2. Any suggestions?

Upvotes: 0

Related Questions