Excel Tip: Counting the number of items in a multi value cell

OPA_T#973_Sept-05-2017

Some iSearch fields contain multiple values when downloaded, for example, the number of

Authors for a Publication, as shown in the example below.

There are ways to answer these questions using iSearch. The unwind feature available in the

.csv download enables the user to create a row for each value in a multi valued cell but this

increases the size of the dataset and may not be helpful depending on the analytical question

being investigated. There is also a “count of pubs” field in iSearch: Grants that that can be

included in a download.

The method and formula

The individual values are separated by a ‘;’ so to know how many unique values there are it is

possible to count the number of times ‘;’ appears in the cell and add one to the total (for the

last value in the list).

There is no simple formula to count the number of times a character appears in a cell, but it is

possible using a combination of Excel formulas:

 The LEN formula counts the total number of characters in a cell.

 The SUBSTITUTE formula replaces a specific character in a cell with another character

So counting the number of characters in a cell and comparing that to the count without any “;”

(it is replaced by “” i.e. removed) gives the number of times “;” appears in the cell. Note that

the last value is not followed by “;” so it is necessary to add one to the result.

Formula 1: =LEN(D2)-LEN(SUBSTITUTE(D2,";",""))+1

This formula works for any field where every record has at least one value (see below, counting

the number of PIs on each grant.

OPA_T#973_Sept-05-2017

The basic version of the formula will not work where there are blank cells, for example counting

the number of PMIDs linked to a Grant as the LEN and SUBSTITUTE formula will return 0 and

then add 1 to it. To correct the results for blank values, the formula above can be nested within

an IF statement:

Formula 2: =IF(I2="",0,LEN(I2)-LEN(SUBSTITUTE(I2,";",""))+1)

So if the cell is empty the count is set to zero, otherwise the formula will count the “;”s and

provide a number of values in the cell. This formula is used to calculate the number of PMIDs in

the example below.

Note that Formula 2 could also be used in place of Formula 1 in the PI example and would give

the same result.

Note: Care needs to be taken when using this formula on iSearch downloads and IMPACII

data:

 There are limits on the number of characters Excel will accept in a field, so for Grants

with thousands of linked publications, the list of PMIDs will be truncated.

 There may be duplicate Authors / PIs linked to a single publication / grant. Counting the

number of items in the field would potentially double count and inflate the numbers.

 PMIDs are linked to core grants so counting multiple ApplIDs within a core grant number

will potentially lead to double counting.

As with any analysis, quality checking of the underlying data is important.

If you are having problems, contact OPA training: [email protected]