Count non-blank cells based on arrayformula in Google Spreadsheets

Question

I am trying to get a count of all non-blank cells in a column that belong to a certain market. The formula I'm using (and that is not working) is:

=ARRAYFORMULA(SUM(('GR Live Campaign Tracker'!$J$3:$J$8607,"US")*(mmult(not(isblank('GR Live Campaign Tracker'!$M$3:$M$8607))))))

I also tried:

=ARRAYFORMULA(SUM(('GR Live Campaign Tracker'!$J$3:$J$8607,"US")*(''GR Live Campaign Tracker'!$M$3:$M$8607)))

which also did not work.

Column J contains countries (e.g. US, UK, Brazil), while column M is either blank or contains a number and I would need to retrieve a count of all cells that contain a number for each market.

Answer 1

Use the following formula to accomplish that.

Formula

=QUERY(
   'GR Live Campaign Tracker'!J:L,                          // data range
   "SELECT J, COUNT(L)  WHERE L IS NOT NULL GROUP BY J",    // select
    0                                                       // headers
)

Screenshot

Example

Added the formula into your example file.