2

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
  • 1
    You should have, instead, edited the original. – ale Dec 27 '17 at 17:51
2

You want a pivot table.

Select the original data, choose Data > Pivot Table.

From the Pivot Table Editor, add a ROW which is "Date", a COLUMN which is "Country", and a VALUE which is "Title"

Result:

Result

  • Thank you @joel-spolsky, pivot table manages to do the job, however, it seems like pivot tables are more difficult to work with using graphs... the 'Grand Total' line and column + impossible to sort Z-A. Any known solution for that? – Elad Ratson Dec 27 '17 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.