CODEWITHSUNDEEP
if-statementgoogle-sheetssumgoogle-sheets-formulaarray-formulas
cerbin
cerbin

Reputation: 1189

Suming multiple cells that may not be number

I am struggling with this for a while and would like to receive your help. What I am trying to do is to sum the values of D column multiplied by value of E column, but not just sum elements of D and then multiply by sum of elements of E but first, multiply elements of each row, so for ex D2 * E2, D3 * E3 etc and then sum up result of multiplication of from each row. I tried to achieve this with arrayFormula and sum, but the problem is that in both column there may appear not a number, but character 'X', so the solution I created will not work. I was trying to use sumif alongside with arrayformula but it is not working and have no idea what to try to change to make this work. The formula I came up with is:

=ARRAYFORMULA(SUMif(D2:D24*E2:E24;"<>*X*"))

Example data:

Upvotes: 2

Views: 46

Answers (2)

player0
player0

Reputation: 1

try simple:

=ARRAYFORMULA(SUM(IFERROR(A1:A*B1:B)))

0

Upvotes: 2

Spainey
Spainey

Reputation: 402

The below code works for me:

=ARRAYFORMULA(SUM(if(D2:D24="X",0,D2:D24)*if(E2:E24="X",0,E2:E24)))

I am using the if() function to check for occurrence of "X" within the array, and replace it with 0 if "X" is found.

Upvotes: 0

Related Questions