авторJohn has a worksheet that he uses for registration of attendees at a conference. Column A has a list of each person attending, and column B has the company represented by each attendee. Each company can have multiple people attend. John can easily figure out how many individuals are coming to the conference; it is simply the number of rows in column A (minus any header rows). The more difficult task is to determine how many companies are going to be represented at the conference.

There are a couple of ways to determine the desired count. First, if there are no blank cells in column B, you can use an array formula (entered by Ctrl+Shift+Enter) such as the following:

=SUM(1/COUNTIF(B2:B50,B2:B50))

авторIf there are blanks in the range (B2:B50 in this case), then the array formula will return a #DIV/0! error. If that case, the array formula needs to be changed to the following:


=SUM(IF(FREQUENCY(IF(LEN(B2:B50)>0,MATCH(B2:B50,B1:B50,0),""),IF(LEN(B2:B50)>0,MATCH(B2:B50,B2:B50,0),""))>0,1))

авторIf you prefer to not use an array formula, you can add regular formulas to column C to do the count. First, sort the table of data by the contents of column B. That way the data will be in company order. Then add a formula such as the following to cell C2 (assuming you have a header in row 1):

=IF(B2<>B3,1,0)

авторCopy the formula down through all the rest of the cells in column C, and then do a sum on the column. The sum represents the number of unique companies attending, since a 1 only appears in column C when the companies change.