CODEWITHSUNDEEP
excelexcel-formulaboolean-logic
user3697625
user3697625

Reputation: 187

How do I write this logic in Excel?

Suppose I have the excel sheet below and I want to calculate the sum of numbers in each row.

Four columns,100 rows. The first row of the first three column contains text (to mark if it's included in the sum). The remaining 99 rows contain numbers. The fourth column is where I want to output the sum of each row.

The logic I want to implement is to check the first row of each column(the text there says "y" for include in sum), then after that calculate the sum of the row including only the cells in the column to be included(if first row in that column has "y"). For the first cell I have the command:

IF(AND(A1="n",B1="n",C1="n"), 0,IF(AND(A1="y",B1="n",C1="n") A2, IF(AND(A1="n",B1="y",C1="n"), B2,IF(AND(A1="y",B1="y",C1="n"), A2+B2,IF(AND(A1="n",B1="n",C1="y"), C2) IF(AND(A1="y",B1="n",C1="y"), A2+C2, IF(AND(A1="n",B1="y",C1="y"), B2+C2, IF(AND(A1="y",B1="y",C1="y"), A2+C2+B2, "error"))) ) ) )) )

It's basically exhausting the ways you can find the sum. I don't see any other way. The main problem I have is spanning this logic to each cell on the 4 column because I always want to look at the first row and take the sum of row 2 then row 3 then row 4 and so on, but when span the code on the other cells, it no longer looks at the A1, it increments the A1 then A2 and so on. I basically want A1,B1,C1 to stay the same for each row but the sum to depend on the row.

How can I do this without manually typing in the logic for all the 99 cells one at a time?

If anything is unclear or more info is need just say. I also want to get the average of the cells in a similar logic.

Upvotes: 0

Views: 158

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60344

If I understand you correctly, simply:

D2:  =SUMIF($A$1:$C$1,"Y",A2:C2)

and fill down. The absolute reference style for range will prevent those references from changing as you fill down, whereas the relative reference style for sum_range will allow those to change to the proper row.

Upvotes: 1

Saic Siquot
Saic Siquot

Reputation: 6513

what about:

IF(A1="y",A2,0)+IF(B1="y",B2,0)+IF(C1="y",C2,0)+IF(D1="y",D2,0)

and so on.

Upvotes: 0

Related Questions