Input has Null and 0 Values in column - Help with the Column update

[EmilDem01]

I have columns with NULL and 0 values, and I need to Total Marks to reflect the Marks as below. Note 1) there will either Maths or Science marks (never both) and 2) If the status is Deleted it will reflect as 0 in the input but I want the total to reflect

Input: 

Status	Maths	Science	Total Marks
Good	100	[Null]	[Null]
Good	100	[Null]	[Null]
Good	[Null]	250	[Null]
Good	200	[Null]	[Null]
Good	[Null]	450	[Null]
Good	[Null]	500	[Null]
Good	[Null]	600	[Null]
Good	[Null]	750	[Null]
Good	[Null]	100	[Null]
Good	0	500	500
Good	750	0	750
Good	600	0	600
Deleted	750	0	0
Deleted	0	500	0
Good	0	500	500
Good	750	0	750
Good	600	0	600

Output: 

Status	Maths	Science	Total Marks
Good	100	[Null]	100
Good	100	[Null]	100
Good	[Null]	250	250
Good	200	[Null]	200
Good	[Null]	450	450
Good	[Null]	500	500
Good	[Null]	600	600
Good	[Null]	750	750
Good	[Null]	100	100
Good	0	500	500
Good	750	0	750
Good	600	0	600
Deleted	750	0	750
Deleted	0	500	500
Good	0	500	500
Good	750	0	750
Good	600	0	600

[EmilDem01]

Also - I would need an addtional column for Subject - if the Marks are in Maths then M or S if Science 

Status	Maths	Science	Total Marks	Subject
Good	100	[Null]	100	M
Good	100	[Null]	100	M
Good	[Null]	250	250	S
Good	200	[Null]	200	M
Good	[Null]	450	450	S
Good	[Null]	500	500	S
Good	[Null]	600	600	S
Good	[Null]	750	750	S
Good	[Null]	100	100	S
Good	0	500	500	S
Good	750	0	750	M
Good	600	0	600	M
Deleted	750	0	750	M
Deleted	0	500	500	S
Good	0	500	500	S
Good	750	0	750	M
Good	600	0	600	M

[BS_THE_ANALYST · Accepted solution]

Quite an interesting one @EmilDem01.

If you just want to add the columns across the rows, it will return null if there's a null in there.

However, the summarize tool behaves as you would expect if you have nulls (it will ignore them and perform the addition): if you were to pivot your data and then summarize. I think the same holds true with pivoting your data (transpose) and then cross-tabbing it; I'd use a record ID before this transformation, though. Note: these routes are dynamic which accounts for more columns needing to be added overtime.

If you didn't want to pivot your data (nor did you want to replace your null values with 0s) you could use this formula below: (this seems to handle nulls which is interesting)

Simply taking the average and then adjusting it by multiplying it by the number of columns used. Note: this can still be dynamic, but requires the use of a batch macro to make it dynamic.

there's many routes to tackle this. I normally go for the pivoting option but this could be problematic for big data. It just depends on your situation.

All the best,

BS