Hi,

I'm desperate for some help with a formula I just cant crack!I have a column (D) which contains names of organisations and an adjacent column containing email addresses (F). I have another column containing a country code which can be one of 6 countries (this is column I, with the options UK, USA, CAN, SA, NZ, AUS).

For some of the organisations I have several email addresses, thus in column D the organisation name sometimes appears twice or more, providing new rows for the email addresses.

I need to create a table which summarises my data telling me how many email addresses I have for each country and how many unique organisations I have for each country.

For the email addresses I have used: =COUNTIF(I1:I3279, "nz")

This works fine, returning the number of times each country code appears in the I column.I now need to create another formula which will count the number of unique organisations I have in each country. I assume it will be something that equates to "countif D is unique and countif I is 'UK'"

Any suggestions?

I'll leave it to you to determine how to set this up, but you need to use SUM as an array formula. Array formulae are entered using Ctrl-Shift-Enter to put {} around the formula. You can not just type the {} to make it an array formula.

For example, if you drag this down along side Column D, it will tell you how many times each item in D1:D9 pairs with NZ in I1:I9.

{=SUM(($D$1:$D$9=D1)*($I$1:$I$9="NZ"))}

Again, the brackets are not typed in but appear after you use Ctrl-Shift-Enter

everytime you edit the formula.You might want to filter columns D and I to someplace else, so that you are dealing with 2 sets of unique values and then run your array formula down next to the list of countries.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History