SUMIF.... but if value is zero how to return a "0" into the cell

[scott5339]

Hi,

I'm working in an excel spreadsheet in Excel 2016 for Mac.

I'm working in a worksheet that is actually a print sheet form that references a bunch of data in my primary worksheet. I set the preferences to leave a cell blank if there was no value (instead of showing a zero). Otherwise there is zeros all over my worksheet everywhere.

But... there are a few instances where I am checking a range to sum a column of numbers if there is an "H" in the range I'm checking.

=SUMIF(H381:H392,"H",G381:G392)

It works perfect. But since it's calculating the number of working hours for the labor code (H), I'd like the cell to show '0' and not just remain blank if the hours for that individual = 0.

Any help would be greatly appreciated.

[FlameRetired]

Hi scott5339. Welcome to the forum.

Since I've never set my preferences to blank if zero and don't have a sample to test (that I can trust) my best guess is try this.

Please Login or Register  to view this content.

or possibly

Please Login or Register  to view this content.

Does that work?

[FDibbins]

Or maybe this?
=if(H381="H",SUMIF(H381:H392,"H",G381:G392),0)

You may need to absolute your ranges there?

[scott5339]

Thank you Dave... Unfortunately that didn't work.

[scott5339]

Hi Ford.
Thanks.... I'll give that a try over the weekend.