# Google Sheets - Problem with ARRAYFORMULA and COUNTIFS Combined

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?

• Thank you @ale, figured out that my first question did not provide enough details so i deleted it and drafted another one... – Elad Ratson Dec 27 '17 at 16:44
• You should have, instead, edited the original. – ale Dec 27 '17 at 17:51

Select the original data, choose `Data` > `Pivot Table`.