Problem description & analysis

The following is data in Excel file:

Column D is the first level grouping field. Column B is the second level grouping field. We are trying to add a new column result, where a value is entered only to the first row in each second level group while other the other rows are left empty. The rule is like this: If a second level group contains more than 3 records, assign 3 to result in the first record; if one contains 3 or fewer records, assign the count result to it. Below is the expected result:

Solution

Use the SPL XLL plug-in of Excel.

Write the formula in a blank cell:

=spl("=E(?).group@o(D,B).run(~(1).result=min(~.len(),3)).conj()",A1:F11)

As shown:

Explanation:

Group the table by column D and B, count records in each group, and assign 3 to result column in the first row in the group if the count is greater than 3, and the real count result to the first result if it is 3 or fewer.

Q & A Collection

https://stackoverflow.com/questions/64223882/count-double-entries-in-2-rows-no-more-than-3-times