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

Do you think the cable television business is going to change drastically in the next 5 years?

Discuss in The Lounge

Poll History