Google-sheets – Google Sheets – Problem with ARRAYFORMULA and COUNTIFS Combined

google sheets

My Sheet1 is being filled automatically by an IFTTT recipe collecting articles matching a subject list of 5 countries, populating a table like this:

Sheet1
        A            B            C
1| Date⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤⁤⁤ ⁤⁤ |⁤⁤ Country⁤⁤ ⁤⁤ ⁤⁤⁤⁤ ⁤⁤⁤⁤ ⁤⁤ |⁤⁤ Title
2| 2017-12-01 |⁤⁤ Iran⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤⁤⁤ ⁤⁤  |⁤⁤ bla bla bla
3| 2017-12-01 |⁤⁤ North Korea |⁤⁤ bla bla bla
4| 2017-12-02 |⁤⁤ Iran⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ |⁤⁤ bla bla bla
5| 2017-12-02 |⁤⁤ Yemen⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤⁤⁤  ⁤⁤⁤⁤ |⁤⁤ bla bla bla
6| 2017-12-03 |⁤⁤ Iran⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤⁤⁤ ⁤⁤ ⁤⁤ |⁤⁤ bla bla bla
7| 2017-12-03 |⁤⁤ Iran⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ ⁤⁤ |⁤⁤ bla bla bla

In Sheet2 I would like to have a table that counts how many articles are written about each country, each day, like this:

Sheet2
       A             B            C         D
1| Date       | Iran       | North Korea| Yemen
2| 2017-12-01 | 1          | 1          | 0
3| 2017-12-02 | 1          | 0          | 1
4| 2017-12-03 | 2          | 0          | 0

To do so, i've placed in Sheet2!A2 the following formula:

=UNIQUE(Sheet1!A2:A)

That gives in Sheet2 Column A a list of all unique date values fed in by the IFTTT recipe into Sheet1.

My challenge is how to count the number of country appearances in Sheet1!B2:B for each unique date.

I tried using the COUNTIFS formula inside an ARRAYFORMULA to automatically populate lines as Sheet1 progresses, like this:

=ARRAYFORMULA(IF(ISBLANK(A2:A), "", COUNTIFS(Sheet1!B2:C,"="&"Iran",Sheet1!A2:A, "="&A2:A)))

However the formula always return the value of 1 for all unique dates.

Any suggestions?

Best Answer

You want a pivot table.

Select the original data, choose Data > Pivot Table.

From the Pivot Table Editor, add as ROW the column "Date", as COLUMN the "Country", and as VALUE the "Title"

Result:

Result

Related Topic