isblank range

[JB2010]

hello


this is simple & i cant work it out. i am annoyed! however, i couldnt find
anything like this listed in the other postings, so i am slightly less
aggrieved!


what i want is a function that says;

if cells B2 to U2 inclusive are all blank, make this cell blank, if not sum
cells B2 to U2 inclusive

seems simple enough, right! so, i have been trying different IF functions,
but i cant get the ISBLANK function to accept multiple cells or a range. I
really dont want to type out a long handed AND formula for every cell in the
range

i cant see how to get ISBLANK to behave, can anyone enlighten me!


many thanks, please let me know if you need anymore info


cheers

jb

 

[Don Guillett]

if all numbers, try using COUNT in your formula
=if(count(b2:u2)>0,"",sum(b2:u2))

 

[Mike]

=IF(COUNTIF(B2:U2,">0")>0,SUM(B2:U2),"")

 

[JB2010]

thanks for that guys, both those work great in this instance.


out of interest, what's up with ISBLANK? why doesnt it want to play ball?

 

[Don Guillett]

One cell only

 

[Dave F]

ISBLANK works over a range if it is entered as an array formula.

=ISBLANK(A1:A100) entered with CTRL +SHIFT + ENTER will resolve to TRUE if
A1:A100 is blank and FALSE if one of the cells in that range is not blank.

Dave