If you have the option of unmerging the cells in column A and normalizing it, then a simple `SUMIFS`

should work. Otherwise, try this:

```
=SUMPRODUCT($C$2:$C$13,
1*($B$2:$B$13=$A16),
1*(ROW($A$2:$A$13)>=SMALL(ROW($A$2:$A$13)+100*($A$2:$A$13=""),B$15)),
1*(ROW($A$2:$A$13)<SMALL(ROW($A$2:$A$13)+100*($A$2:$A$13=""),B$15+1)))
```

**Result:**

```
╔════════╦════╦════╗
║ Day ║ 1 ║ 2 ║
╠════════╬════╬════╣
║ Apple ║ 60 ║ 90 ║
║ Orange ║ 0 ║ 85 ║
║ Pear ║ 75 ║ 0 ║
╚════════╩════╩════╝
```

**Source:**

Modified the formula found here:

**Explanation:**

The core part is the `SMALL`

function

```
SMALL(ROW($A$2:$A$13)+100*($A$2:$A$13=""),B$15)
```

It creates an array of row numbers for a specified range, where cells not containing anything (`=""`

) have a value that's 100 more than the corresponding row number (`+100*`

). On the other hand, the cells failing the condition (i.e., non-blank cells) will only be assigned their row numbers. This array is passed on to the `SMALL`

function with `B$15`

as the second argument, which tells it to fetch the *kth* smallest item in the array.

So, we want to sum the rows where the row starts with the day number, which is what this part does:

```
1*(ROW($A$2:$A$13)>=SMALL(ROW($A$2:$A$13)+100*($A$2:$A$13=""),B$15))
```

However, we also want to restrict the totalling up to the cell directly above the next non-blank cell; hence, the `<`

operator and `B$15+1`

(i.e., *(k+1)th* smallest item) in the following part:

```
1*(ROW($A$2:$A$13)<SMALL(ROW($A$2:$A$13)+100*($A$2:$A$13=""),B$15+1))
```