IF Statement is FALSE instead if Zero-CodePudding

Here is some sample data in a Google Sheet:

https://docs.google.com/spreadsheets/d/1cIP118xPmNxMVv0vlpxZ7igp5pVr000f0H8RRILCQIw/edit#gid=0

I would like to return the sum of Pick Up if a column contains Pick Up, and the sum of Actuals if it doesn't (in that case all texts in the column would be Actuals).

Here is the formula I have tried: In this case when the ( "Pick up" Sum range is all 0, it returns it as False but I need it to be $0.00)

=IF(
  SUMIFS(C1:C5,B1:B5,"Pick Up"),
  SUMIFS(C1:C5,B1:B5,"Pick Up"),
  SUMIFS(C1:C5,B1:B5,"Actuals")
)

CodePudding user response：

Take a look at this Google Sheet:

https://docs.google.com/spreadsheets/d/13a4ZcgFu-yRiyZohXmh0aDnW0EheTOGElXvyQ0WHeHI/edit#gid=0

You want to make your condition a COUNTIFS, so that if Pick Up is found in the column it returns the first option and if not the second:

=IF(
  COUNTIFS(B1:B5, "Pick Up")>1, 
  SUMIFS(C1:C5, B1:B5, "Pick Up"), 
  SUMIFS(C1:C5, B1:B5, "Actuals")
)

Actually, the SUMIFS in the second option for Actuals isn't really necessary. If the only thing in the column is Actuals, then you can just use SUM:

=IF(
  COUNTIFS(B1:B5, "Pick Up")>1, 
  SUMIFS(C1:C5, B1:B5, "Pick Up"), 
  SUM(C1:C5)
)

Hope this helps.

The original answers are below. I edited them after sample data and clarity on the question was provided. They are left for historical purposes, or if someone looks at the edit history on the question, or if the edit isn't accepted.

EDIT: based on feedback in the comment. If you want to return the sum of Actuals when there are no Pick Ups, change the sumifs condition to countifs:

=IF(
  COUNTIFS(X97:X126,$D97:$D126,"Pick Up") > 0,
  SUMIFS(X97:X126,$D97:$D126,"Pick Up"),
  SUMIFS(X97:X126,$D97:$D126,"Actuals")
)

Original answer:

It's a little hard without some actual data, but I think your issue is that if your condition (the sumifs using "Pick Up") returns 0 then that's a falsy value, so it goes to the second output.

=IF(
  SUMIFS(X97:X126,$D97:$D126,"Pick Up"),
  SUMIFS(X97:X126,$D97:$D126,"Pick Up"),
  SUMIFS(X97:X126,$D97:$D126,"Actuals")
)

Try this instead:

=IF(
  SUMIFS(X97:X126,$D97:$D126,"Pick Up") >= 0,
  SUMIFS(X97:X126,$D97:$D126,"Pick Up"),
  SUMIFS(X97:X126,$D97:$D126,"Actuals")
)

That way if the sumifs returns 0 (or greater) then you will get an explicitly TRUE value to use in the first part of the if, instead of a number that Excel is going to try to interpret as boolean.