keep blank cells blank & 0 shows that is is 0?

As advised in the yellow banner at the top of this page, providing a sample workbook is a good idea.

HOW TO ATTACH YOUR SAMPLE WORKBOOK: Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Use, for example

Formula:

Please Login or Register  to view this content.

TMS has shown the correct strategy, but I'm interested in exactly what your formula is, in particular whether it is dealing with arithmetic or text.

With 365, your formulae can be written more briefly/concisely like this:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Logically, if you have a SUMIF, the result will be zero or a value other than zero. You cannot get a null value out of a SUMIF (AFAIK). So, if you test for zero, you will get a null value returned (if the result is zero), or the value of the SUMIF. If you test for a null value, you will always get the value of the SUMIF, regardless of its value. Hence you see 0 when the SUMIF evaluates to zero.

If that doesn't help, please upload a sample file.


Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Just to reiterate: a SUMIF will not return a null value.

Here's a sample file.

I'm struggling to understand exactly what is going on here. I mean, I can understand the formula as written, but I'm wondering exactly what is meant by "cells that=blank to stay blank."

Maybe this will help clarify -- exactly what "cell that is blank" are you referring to?

Wild guess: The current SUMIF() is summing the values in column E of "Set 1" whenever the value of column C of "Set 1" matches the value in B30 of "Tables per min." I'm guessing that "Set 1" is a "database" of sorts, and it seems unlikely that all of the cells in columns C or E will be truly blank. Wild guess is that you want a blank in B30 of "Tables per min" to be propagated as a blank. Something like =IF('Table (per min)'!$B30="","",SUMIF(...)). This variation only performs the SUMIF() when there is something meaningful in B30. If B30 is blank (or null string), then the SUMIF() is ignored.

Isn't that saying, if there are no matching values, the FILTER will return a null. The SUM will then return #VALUE! and the IFERROR will give you a null value back.

Isn't that the same as MrShorty's suggestion of =IF(B30="","","Original formula")

Ah, ok. I was obsessing on the blanks. In my testing, I have blanks in column C with values in column E.

It would all be so much easier if the OP would provide a small sample worksheet as requested instead of us having to simulate it.