# Sumifs in merged cells with criteria

#### Villalobos

##### Active Member
Hello,

I would have a question regarding the "sumifs" of merged cells, it is inspired by a formula challenge in 2013. Here is the path to the thread: https://chandoo.org/forum/threads/formula-challenge-021-sumif-in-merged-cells.11927/

The question: how to sum the values with criteria?
The criteria: if one of item is repeated in range X (it has always the same value in range V) then the formula should take into account only one time its value when the sum is calculated for the item in range D.

You can find attached the sample file with the desired manual results.

Non-array is preferred.

Thank you for any solution you can provide.

#### Attachments

• 11 KB Views: 5

#### vletm

##### Excel Ninja
Villalobos
The question: how to sum the values with criteria?
... This is one possible way to do it

#### Attachments

• 12.9 KB Views: 9

#### bosco_yip

##### Excel Ninja
Another formula option without helper

In F6, array ("Ctrl"+"Shift"+"Enter") formula copied down :

=SUM(IFERROR((LOOKUP(ROW(D),ROW(D)/(D<>""),D)=E6)*V*(MATCH(X,X,0)=(ROW(X)-1)),0))

#### Attachments

• 12.2 KB Views: 7

Thank you guys!