Forum Discussion
Solved
Sum of All Product?
Hi, How do I get the total of all apples in the below table in one simple formula? Product QTY Product QTY Product QTY Apple 1 Apple 1 Apple 7 Apple 2 Apple 1 Apple 2 ...
Qinnab You could use SUMIF as demonstrated in the attached file. It's not very elegant though and perhaps your real life situation isn't as simplistic as your example suggests.
One more similar variant for the collection
References:
=SUM(
MMULT( --( COLUMN($B$2:$G$2) = COLUMN($B$2) ),
MMULT( TRANSPOSE( --($B$3:$G$12=$I2) ),
IF( ISNUMBER($B$3:$G$12), $B$3:$G$12, 0 )
) )
)Named ranges:
=SUM(
MMULT( --( COLUMN(Headers) = CELL("col", Headers ) ),
MMULT( TRANSPOSE( --(Data=$I2) ),
IF( ISNUMBER(Data), Data, 0 )
) )
)Table:
=SUM(
MMULT( --( COLUMN(Shops[#Headers]) = CELL("col", Shops[#Headers] ) ),
MMULT( TRANSPOSE( --(Shops=$I2) ),
IF( ISNUMBER(Shops), Shops, 0 )
) )
)
That's pretty devious. The conversion to a Table should make the worksheet far easier to use though.
That was only to illustrate differences in using range references, named ranges and tables when the size of the range is relatively dynamic. For that the person need to play with variants using this or that formulae suggested.