sumif for non-blank criteria

Greetings,

I'm trying to figure out how to get sumif to sum for non-blank entries- e.g.
sumif(A1:A5,"not isblank", B1:B5).

Any help greatly appreciated...

Try this

Please Login or Register  to view this content.

Here is another one, array formula

Please Login or Register  to view this content.

*Press Ctrl + Shift + Enter.

The second solution offered above will produce a different result to the first offered if the range in A contains null values.

Another alternative to the first using SUMPRODUCT approach:

=SUMPRODUCT(--(LEN(A1:A5)>=1),B1:B5)

Neither this nor sglife's first solution will treat a cell containing say a space (and nothing else) as a blank... if you had this eventuality (and you wanted to tread a cell with just a space as a true blank) you could adjust the above to:

=SUMPRODUCT(--(LEN(TRIM(A1:A5))>=1),B1:B5)

In these cases I like to use a mask. If you would like to use the ISBLANK function this can be used

Please Login or Register  to view this content.

ISBLANK still counts Nulls as non-blanks which may or may not be correct for OP... I would guess not. I'm not entirely sure what you mean when you say you're using a mask here ?

DonkeyOte,

In the question itself was "not isblank" so allthough you are right I've used it.

With MASK I mean the multiplivation within Sumproduct. Multiplying with ones and zero's can be seen as a mask. Same for binary calculation 11011101 & 11110000 = 11010000. It's just a naming. Sorry for the confusion.

Thanks all! And to clarify, the sum needs to be performed for any whitepsace.

DonkeyOte- I checked Excels help on Sumproduct; can you explain how/why you can stuff a condition in for one of the arrays, and what the "--" syntax signifies (or point me to a description)?

Sumproduct works like this
Array A = {1,2,0,4}
Array B = {5,6,7,8}

Sumproduct(A,B) = SUM(5,12,0,32) =49

The "--" is used to change True, false into 1 and 0. Multiplying with 1 (*1) or adding 0 (+0) are sometimes used as well, but most use "--"

see also here:

http://www.excelforum.com/developmen...ct-matrix.html

I have actually built a utility (Excel Add-in) to show how any given Sumproduct formula generates it's result.

EDIT: having re-read your post...

Essentially what you're trying to generate is sum of B where A is not blank... using SUMPRODUCT you can use a test on A as 1 array and the values in B as another... when you have a test as the first array it will generate a boolean result in each record that is to say for each row in the range the result is either TRUE and meets the condition or it's FALSE ... booleans when coerced to integers (using the -- method in this instance) will generate 0 for FALSE or 1 for TRUE. In the resulting matrix then you gave a list of values {1,0,1},{10,20,30} which as previously outlined will generate a total of 40 -- being 1*10 + 0*20 + 1*30

Does that help ?

Using conditions in Sumproduct is why it's probably the most versatile function at your disposal... note however that using a lot of them is likely to have a detrimental affect on the performance of your file so use sparingly and where possible be *creative* to avoid need for Sumproduct.

Here is an example post where using Sumproduct became an issue and switching methods and being *creative* led to massive performance gains:

http://www.excelforum.com/excel-work...rize-data.html