I have a sheet that looks like this:
estimate | actual | name
10 | 1 | Bob
20 | 9 | Sue
30 | | Bob
40 | 3 | Sue
How can I get a sum of where the name is "Bob", and the sum of the actual column if it exists, but if not use the estimate column instead (i.e. 1 + (blank -> 30) => 31)
I keep running into issues with mismatched range sizes – is there any way to do this in one formula without adding lots of "dummy" columns (i.e. if actual , actual , else estimate) and so on? or without needing to create a "Bob" column and always check against that?
Best Answer
This can be done with two
sumifs
formulas:The first adds "actual" quantities (column B) where C is "Bob". The second adds "estimates" (column A) where C is "Bob" and B is blank.