Using isblank with if statements keeps returning FALSE as answer

[Guest]

I'm trying to sum a column of numbers by month, but want to insert "0" if there are blanks for any month in a cell. The column (A) is set up as a pick list to prohibit data entry error. This is what causes the FALSE error when doing a simple if statment combined with a sum to get each month (e.g. =if(a1:a150="january",sum(d2:d150)).

So I changed the statement to try and insert a "0" if there are blanks:

=IF(OR(ISBLANK(A1:A150),(0)),IF(A1:A150="january",SUM(D1150))).

With this I continue to get FALSE. Obviously I will make them absolute references once I get the formula corrected.

So in a nutshell, what I want to do is that IF A1-A150 is blank then put a zero in the cell, but IF A1:A150=January(or whatever month), then I want to sum D1-D150.

What am I doing wrong in the above formula?

Thanks in advance...steve

 

[Jason Morin]

Try SUMIF:

=SUMIF(A1:A150,"january",D1150)

HTH
Jason
Atlanta, GA

-----Original Message-----
I'm trying to sum a column of numbers by month, but want

to insert "0" if there are blanks for any month in a cell.
The column (A) is set up as a pick list to prohibit data
entry error. This is what causes the FALSE error when
doing a simple if statment combined with a sum to get each
month (e.g. =if(a1:a150="january",sum(d2:d150)).

So I changed the statement to try and insert a "0" if there are blanks:

=IF(OR(ISBLANK(A1:A150),(0)),IF(A1:A150="january",SUM (D1150))).

With this I continue to get FALSE. Obviously I will make

them absolute references once I get the formula corrected.

So in a nutshell, what I want to do is that IF A1-A150 is

blank then put a zero in the cell, but IF A1:A150=January
(or whatever month), then I want to sum D1-D150.

 

[Bernard Liengme]

Hi Steve,
I worked with a short list of 8 months in A1:A8 and numbers in B1:B8 but you
can convert to your scenario.
Couple of things wrong:
1) If there is a blank in A1:A8 then =ISBLANK(A1:A8) will return TRUE, but
=IF(ISBLANK(A1:A8),0,"x") will return x not 0 unless the blank is in A1!
This is because the IF looks at only one Boolean value even when the formula
is entered as an array formula.
2)Likewise you cannot use IF to conditional sum but you can use SUMIF.
=SUMIF(A1:A8,"jan",B1:B8) says for each cell in A1:A8 if the value is jan
then add the corresponding B cell.

I expect others will give you a SUMPRODUCT formula but this one works for
me. The first part returns True (treats as 1 when part of a calculation) if
there are no blanks, otherwise it returns False (0). The SUMIF work
explained above.

=NOT(ISBLANK(A1:A8))*SUMIF(A1:A8,"jan",B1:B8)

Best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

I'm trying to sum a column of numbers by month, but want to insert "0" if

there are blanks for any month in a cell. The column (A) is set up as a pick
list to prohibit data entry error. This is what causes the FALSE error when
doing a simple if statment combined with a sum to get each month (e.g.
=if(a1:a150="january",sum(d2:d150)).

So I changed the statement to try and insert a "0" if there are blanks:

=IF(OR(ISBLANK(A1:A150),(0)),IF(A1:A150="january",SUM(D1150))).

With this I continue to get FALSE. Obviously I will make them absolute

references once I get the formula corrected.

So in a nutshell, what I want to do is that IF A1-A150 is blank then put a

zero in the cell, but IF A1:A150=January(or whatever month), then I want to
sum D1-D150.

 

[Jerry W. Lewis]

1) If there is a blank in A1:A8 then =ISBLANK(A1:A8) will return TRUE, but
=IF(ISBLANK(A1:A8),0,"x") will return x not 0 unless the blank is in A1!
This is because the IF looks at only one Boolean value even when the formula
is entered as an array formula.

That is not my experience in Excel XP, 2000, 97, 95, or 4.

Jerry

 

[Guest]

Thanks so much...much more elegant solution...stev

----- Steve wrote: ----

I'm trying to sum a column of numbers by month, but want to insert "0" if there are blanks for any month in a cell. The column (A) is set up as a pick list to prohibit data entry error. This is what causes the FALSE error when doing a simple if statment combined with a sum to get each month (e.g. =if(a1:a150="january",sum(d2:d150))

So I changed the statement to try and insert a "0" if there are blanks

=IF(OR(ISBLANK(A1:A150),(0)),IF(A1:A150="january",SUM(D1150))).

With this I continue to get FALSE. Obviously I will make them absolute references once I get the formula corrected

So in a nutshell, what I want to do is that IF A1-A150 is blank then put a zero in the cell, but IF A1:A150=January(or whatever month), then I want to sum D1-D150

What am I doing wrong in the above formula

Thanks in advance...steve