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:
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.