How to have a dynamic sum function

[metal.stuff]

Hello all,
Im looking to have a dynamic sum function, meaning I know the beginning of the sum array, but the second part of the array will be based on a table.
Please see the attached example, it explains the way I mean it, and hopefully someone will be able to help meout.

Cheers.

 

[Villalobos]

Hi,

The "Item" in merged cells, if it's possible then un-merge them -> insert Table -> and use SUMIFS function.

Check the attached file.

 

[NARAYANK991]

Hi ,

Another option :

=SUM($L$3:INDEX($L$3:$L$11, MATCH($C$5, $I$3:$I$11,0) + MATCH($D$5,$J$3:$J$11,0) - 1))

Of course , this will work only if each of A , B , C has the same number of items.

Narayan

 

[bosco_yip]

Summing with item in merged cells without un-merged them.

1] Criteria C5 : enter A, B or C.

2] Total D5, enter formula :

=SUMPRODUCT((SUBTOTAL(3,OFFSET(I$3,,,ROW($1:$12)))=MATCH(C5,{"A","B","C"},0))*L$3:L$14)

Regards
Bosco