Starting with this list of countries and dates:
| date | country |
|---|---|
| 01.04.2022 | Norway |
| Sweden | |
| Norway | |
| 02.03.2022 | Denmark |
| 03.03.2022 | Norway |
I want to pick the UNIQUE countries from the list but only those that have a date. I also want to run a COUNTIF on the countries but still only the ones that have a date.
Running a UNIQUE and COUNTIF wintout the dates will give this:
| UNIQUE(country) | COUNTIF(country;row) |
|---|---|
| Norway | 3 |
| Sweden | 1 |
| Denmark | 1 |
The result I need is only getting data from the rows that have a date, getting this result:
| country | count |
|---|---|
| Norway | 2 |
| Denmark | 1 |
How can I run UNIQUE and COUNTIF on a column based on the data in adjoined column?
CodePudding user response：
This is the kind of thing that QUERY() is designed for. In a brand new tab in cell A1
=QUERY(Sheet1!A:B,"select B,COUNT(B) where A is not null group by B order by COUNT(B) desc")
or if you're in some locales in western europe:
=QUERY(Sheet1!A:B;"select B,COUNT(B) where A is not null group by B order by COUNT(B) desc")