I have a table with 'x' representing that the number on the left must be added at a total.
First row have 1 'x' with a value of 3, so the total of the row is 3, the second row don't have a 'x' so the total is 0, and the third row have 2 'x' with a value of 6, so the tot is 12.
After that I sum all total to get the 39.
I wanted to get the final total without the intermediate results. I know we can use array formulas, but I can't find the right formula. I tried {=SUM(COUNTIF(B2:E14,"x")*A2:A14)}
but it doesn't work.
Have you an idea ?
CodePudding user response:
Using MMULT()
• Formula used in cell G15
=SUM(MMULT(N(B2:E14="x"),{1;1;1;1})*$A$2:$A$14)
Or,
=SUM(MMULT((B2:E14="x")*$A$2:$A$14,{1;1;1;1}))
Or, you can use SUMPRODUCT()
or SUM()
Function as well,
• Formula used in cell H15
=SUM(($B$2:$E$14="x")*$A$2:$A$14)