Group and Summarize rows and add different words after different counts

An Excel table has two columns:


A

B

1

Apples

3

2

Apples

0

3

Bananas

1

4

Bananas

6

5

Cantaloupe

0

6

Kiwis

2

7

Kiwis

2

8

Kiwis

1

Task: Group the table by the 1st column, and add 1 if the 2nd column in the current group is greater than 0; otherwise, do not add 1. If the count equals 1, add the word “Occurrence” after it; if it doesn’t, add its plural form “Occurrences” after it. Below is the expected result:


A

B

1

Apples

1 Occurrence

2

Bananas

2 Occurrences

3

Cantaloupe

0 Occurrences

4

Kiwis

3 Occurrences

Use SPL XLL to do this:

=spl("=E@b(?.group(~1;(t=~.count(~2>0)) / if(t==1,"" Occurrence"","" Occurrences"")))",A1:B8)

imagepng

group() function groups rows and handle each group of data. E@b removes column titles. ~1 represents the 1st child member of the current member in a sequence.

Source:https://stackoverflow.com/questions/78117452/excel-count-if-relative-reference