ISBLANK once again: selecting cells for SUMIF

if the cell is blank if adds to the sum as 0 (zero) so is there really a need to identify it as blank for the sum?

e.g.:

F2 sums the cells in olumn D only if there is a "1" in the corresponding row of column C. cell D6 is not included because there is not a "1" in the C6. D9 is included but is blank (so it adds 0).

F2=SUMIF(C2:C9, "=1", D2:D9)

The blanks are in column I but the values to be summed (or not) are in column K.

Don't use blank (or empty) as the condition in column I. Use a " " (space), or "0" (zero) or "NA", then you can use that as the condition to sum.

You could also add a new column that doctors the codes so blank is translated to "B":

column E is new and can be hidden:

E2=IF(ISBLANK(C2), "B", C2)

select E2 and fill down

G2=SUMIF(E2:E9, "=1", D2:D9)

G3=SUMIF(E2:E9, "=B", D2:D9)

I am not aware of a way to include a function as part of a condition directly. You can use a temporary column (like I demonstrated).

=SUMIF(I103:I110,"",K103:K110) is what you are looking for.

At least its working in Number 3.2 (latest App Store version)

Hi Ricardo,

Please post a screen shot to explain why you need ISBLANK

Or maybe this will work:

Formula in Footer Cell C8

=SUMIF(B,"=TRUE",C)

The sum is 20 because only rows 2 and 3 fulfil the requirement of TRUE in B whilst having a numerical value in C.

I tested this by entering "invisible" characters (space, option enter, option tab) into the blank cells C4 and C5. The sum remained the same.

Even with text:

I am still puzzled why you need to test for blank cells...

Regards,

Ian.

I'm guessing you're probably not working on this any more, but for others they might be interested in `=SUMIF(I103:I110,"=",K103:K110)`. And it seems reasonable to me to want to sum values because an action hasn't been taken which would mean that the cell would be empty.

Alexey... This worked for me too, with Numbers '09. Thanks! (The forum isn't positioning this as a subthread to your specific post, but this is in reply to your suggestion of using `=SUMIF(I103:I110,"",K103:K110)` as the formula setup.)

feus4177... I just tried your method too, with the "=" ... and that worked also. (Again, with Numbers '09.) Not sure if one of these approaches (with or without the equal sign in the middle) is more robust, as I am using this in a pretty simple spreadsheet, but good to know these are options for setting up this kind of formula.