1

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.

  • 1
    Best is to share a doc with us. – Jacob Jan Tuinstra Jan 3 '14 at 12:56
  • Ok. I have shared a sheet here with two tabs. One contains the two columns I am trying to work with (GR Live Campaign Tracker) and one the formulas I have added. link – Chris Jan 3 '14 at 14:12
  • Any ideas? Anything would help. – Chris Jan 6 '14 at 8:31
0

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

enter image description here

Example

Added the formula into your example file.

  • Only question now: It seems to count duplicates. Can something like "CountUnique" be added if it is done in this way? – Chris Jan 6 '14 at 12:11
  • @Chris That's a different question, please re-ask and put the expected outcome next to column E if you like.... – Jacob Jan Tuinstra Jan 6 '14 at 12:51

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.