Is this a static table, or a pivot table or categorized. It looks like categorized, which is fine, just adds a degree of complication.
Ultimately, your problem stems from your non-consecutive ranges.. the first thing you have to do is separate the Expected values from the Actual values.
Assuming you're running Numbers 14.4, that's actually quite easy using the FILTER() function.
=SUM(IF(FILTER(C,B="Actual",)>0,FILTER(C,B="Actual"),FILTER(C,B="Expected","")))
It's a little hard to decipher, but easier to understand if you break it out using LET()
LET() allows you to create variables for subsets of data/calculations, etc., so it's possible to 'find all Expected' values and call them one thing, then 'Find all the Actual values' and call them something else. Then we can do the comparisons.
=LET(expected,FILTER(C,B="Expected",0),
actuals,FILTER(C,B="Actual",0),
SUM(IF(actual>0,actuals,expected))
)
This runs a FILTER() against column C (the 'January' values) and extracts the values where column B is "Expected". This returns an array of 'Expected' values and puts them in a variable called 'expected'.
It then runs another FILTER() against column C, this time looking for values where column B is "Actual", and puts these results in an array called 'actuals'
Now we can run a simple SUM() where we compare the actuals values. If it is greater than 0, it returns the actuals value, otherwise it returns the expected value. This is a common pair-wise function that compares two arrays.
This SUM() function is the same as the one-line solution above, but it uses the variable names to make it a little easier to follow what's going on. Both should have the same result.
Note that for this to work properly, there needs to be the same number of 'Expected' and 'Actual' values. That shouldn't be a problem here, but something to be aware of.
