1

So I got a excel sheet that lists (amongst other things) organizations and the year they were active in. Some organizations were active more than once a year. It looks like this:

YEAR -- ORGANIZATION

2015 --- Orga A
2015 --- Orga A
2015 --- Orga B
2014 --- Orga C
2014 --- Orga C
2013 --- Orga D
2013 --- Orga D

I'm trying to figure out a formula that will give me the number of unique organizations active per year (in the example: 2 in 2015, 1 in 2014, 1 in 2013).

What I've tried: I created a new column in C, and entered this formula in each row:

=COUNTIF($B$2:B*X*,B*X*)=1

X = 2 (B2), in the first row, and than rises by 1 per row (e.g. B2, B3, B4) That will output whether it is the first time that the organization was active (as YES/NO).

With this I can then create a simple COUNTIF formula that will count the number an organization was active. The problem is however that this refers to whether the organization was active in all years more than once, not per individual year.

Writing the above mentioned function for every year is not an option, as the sheet should be sort of "self-sustaining", as it is an on-going list that will need to accommodate future years.

I feel like this could be solved with an array formula and an IF function, but I'm unsure how... Any input would be hugely appreciated! (:

  • You could totally use a PIVOT TABLE! – Raystafarian Aug 14 '15 at 10:55
  • How so? If I use the Pivot Table's count function I do not eliminate the duplicates. – Kilian Aug 14 '15 at 12:46
  • Have you tried Remove Duplicates? – Kyle Aug 14 '15 at 13:02
  • Nope, how could I use that specifically? – Kilian Aug 15 '15 at 9:21
0

I found it very strange that nobody answered to this... Well, I'll try then.

Ok, if you're not fond of pivot tables I can suggest you a couple of solutions that doesn't make use of them.

FIRST WAY

The prerequisites are that you have the list of all possible organization names as well as a list of all the years you want to check for the number of unique organizations active.

Example

In the example I showed, I put your list in column A (range A1:A7), the list of possible organization names in the range C2:C5 and the years from 2013 to 2015 in D1 to F1 cells.

Now that you have this you can obtain what you need to know following these steps:

  1. in D2 put =IF(IFERROR(MATCH(CONCATENATE(D$1," --- ",$C2),$A$1:$A$7,0),0)>0,1,0) then copy the formula until F5. This will create a grid of 0s and 1s with 1 meaning that organization was active in the reference year and 0 meaning it wasn't.

  2. Then all you need to do is simply getting sums in row 6 from D to F (simply put =SUM(D2:D5) in D6 and then copy formula until F6).

Otherwise, if you don't need or don't want the grid, you can use a CSE/array formula (can find some links to know more about them in another of my answers).

In the example, in D7 I wrote =SUM(IF(IFERROR(MATCH(CONCATENATE(D$1," --- ",$C2:$C5),$A$1:$A$7,0),0)>0,1,0)) then pressed ctrl+shift+enter and copied the formula until F7. (Just note that steps 1. and 2. are not necessary at all to get the desired results in this case.)

SECOND WAY

Finally, if you can't get a list of all organizations easily, assuming you can only compile the list of the years you are interested in checking for, a possible solution could be the following:

  1. Select all your data range (in my example it's A1:A7) and go to "Data" set and select "Remove Duplicates".

Excel Data Remove Duplicates

In this example only 4 rows should remain and they will be unique combinations of year --- organization name (an example of how Remove Duplicates work) so now we can simply "count" the number of times a year appear in the remaining rows data range. The number of rows that will remain after removing duplicates isn't really important as we will assume the entire A column as data range.

  1. So, after having removed duplicates, what is left to do is use the formula (for example in D8 and then copying to F8) =COUNTIF($A:$A,CONCATENATE(D$1," --- *")) (just note that asterisk in the formula is an Excel wildcard, can find some links to know more about them in the same other answer I mentioned before)

and that's it.

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.