Sumif all Null Values

[SanCarlosCyclist]

I have a column with a formula that on certain conditions populates
the cell with a null value. ("")
Is there a way to use Sumif that sums an adjacent column when the
first column is null?

Example
A B
1 12 50
2 200
3 4 300
4 100

I want to do a sumif for column A when value = "" then sum column B.
The result would be 300.
One complication is that column A is an "If " formula that sometimes
populates a cell with ""

Can this be done. I appreciate your help.

 

[Claus Busch]

Hi Cyclist,

Am Fri, 2 Jul 2010 13:01:46 -0700 (PDT) schrieb SanCarlosCyclist:

Example
A B
1 12 50
2 200
3 4 300
4 100

I want to do a sumif for column A when value = "" then sum column B.
The result would be 300.

try:
=SUMIF(A:A,"",B:B)


Regards
Claus Busch

 

[Dave Peterson]

=SUMIF(A:A,"",B:B)

This will treat those empty cells and those cells containing the "" string
exactly the same.

I'm not quite sure if that's what you want -- or if that's a problem.

If you really want to distinguish between the cells with "" and really empty
cells, you could use something like:

=SUMPRODUCT(--(A1:A20=""),--(A1:A20=0),B1:B20)

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===============

ps. If you convert the formulas that evaluate to "" (like ="") to values, then
these cells are not empty! You'd have to clean them up to get the results you want.

 

[SanCarlosCyclist]

Neither of these worked. Let me list the exact formulas:

In cell Al21, I have the following formula:
=IF(ISNA(INDEX(I21:T21,MATCH(TRUE,I21:T21<>"",0))/LOOKUP(2,1/
(E21:H21),E21:H21)-1),"",INDEX(I21:T21,MATCH(TRUE,I21:T21<>"",0))/
LOOKUP(2,1/(E21:H21),E21:H21)-1)
I copy this formula through AL632

In Call AM 21, I have the following formula:
=IF(AL21="",0,$AC21)
I copy this formula down through AM632


I want to do a sumif that goes through all of the AL values and finds
a "" result and adds the values in AM.
SUMIF(AL21:AL632,"",AM21:AM632) does not work. I get a value of zero.
Suggestions?

 

[Claus Busch]

Hi Carlos,

try this one:
=SUMPRODUCT((A1:A20=0)+(A1:A20=""),B1:B20)


Regards
Claus Busch

 

[SanCarlosCyclist]

Hi Carlos,

try this one:
=SUMPRODUCT((A1:A20=0)+(A1:A20=""),B1:B20)

Regards
Claus Busch

Hi Claus this seemed to work I would like to do one more thing. Assume
that Column C is populated with a letter between A and F.

Can you modify the formula to sum only when a1:a20 = "" AND
C1:c20="A"

Is this posible? Again thanks much for helping me.

 

[Claus Busch]

Hi,

Am Fri, 2 Jul 2010 14:13:03 -0700 (PDT) schrieb SanCarlosCyclist:

Can you modify the formula to sum only when a1:a20 = "" AND
C1:c20="A"

=SUMPRODUCT(--(A1:A20=""),--(C1:C20="A"),B1:B20)


Regards
Claus Busch

 

[SanCarlosCyclist]

Hi,

Am Fri, 2 Jul 2010 14:13:03 -0700 (PDT) schrieb SanCarlosCyclist:


=SUMPRODUCT(--(A1:A20=""),--(C1:C20="A"),B1:B20)

Regards
Claus Busch

Hi Klaus, when I apply the formula, I get a value of zero. Is it
because the values in column A are not actually null? I have an IF
statement formula that sometimes populates cells in column A with the
value of "". Is there a fix?

 

[SanCarlosCyclist]

Hi Klaus, when I apply the formula, I get a value of zero. Is it
because the values in column A are not actually null? I have an IF
statement formula that sometimes populates cells in column A with the
value of "". Is there a fix?

Hi Claus, I got it to work. Thanks so much.