I have 2 ranges and I would like to apply the MAX function to them and have a range again

This is my problem: I have two ranges and I would like to get the MAX value for each row and have a again a range to SUM.

	A	B	C
1	Available	Needed	Missing=MAX(0,Needed-Available)
2	10	5	0
3	10	15	5
4	5	10	5
5	15	10	0
6	total	SUM(C2:C5)	10

I would like to calculate the total of Missing without the Missing column, I thought I could do something like this: SUM(MAX(0,B2:B5-C2:C5)) but it doesn't work.
Actually I solved it with a IF function mimicking the MAX function: SUM(IF(B2:B5-C2:C5<0,0,B2:B5-C2:C5)) but I was wondering if it was possible to use the MAX function or if there is another function to calculate the MAX of a range and return a range again.

Edit:
I have found another way to do it with the MAX function, although I find it even less "readable" than the version with the IF function.
This solution uses the BYROW function: SUM(BYROW(B2:B5-C2:C5,LAMBDA(row,MAX(0,row))))

I was hoping to find something simple and "readable" ...

Hi,
Something like this?

Formula:

Please Login or Register  to view this content.

Originally Posted by tanasedn

Formula:

Please Login or Register  to view this content.

Note: This solution works in 2021 and 365 as shown. However, to use this in 2016 you will have to enter it as an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.