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?


This can be done with two sumifs formulas:

=sumifs(B2:B, C2:C, "Bob") + sumifs(A2:A, C2:C, "Bob", B2:B, "")

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.

  • This works nicely, thanks! Jun 2 '16 at 3:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.