# Countif unique and if function

June 24, 2009 at 02:45:11
Specs: Windows XP

 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?

See More: Countif unique and if function

#1
June 24, 2009 at 05:57:46

 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.

Report •
Related Solutions