CODEWITHSUNDEEP
excelexcel-formula
Maxime Charrière
Maxime Charrière

Reputation: 785

SUM of COUNTIF on multiple row with Array formulas

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

Upvotes: 1

Views: 343

Answers (2)

Manoj
Manoj

Reputation: 461

my ans

just look at it..

=SUM(IF(B2:E14="x",A2:A14,0)) =SUM(MMULT(--(TRANSPOSE(B2:E14)="x"),A2:A14))

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27448

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)

Upvotes: 2

Related Questions