Home > Blockchain >  SUM of COUNTIF on multiple row with Array formulas
SUM of COUNTIF on multiple row with Array formulas

Time:12-27

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 ?

enter image description here

CodePudding user response:

Using MMULT()

enter image description here


• 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,

enter image description here


• Formula used in cell H15

=SUM(($B$2:$E$14="x")*$A$2:$A$14)

  • Related