IF statement based on "nonblanks"

  • Thread starter Thread starter sowetoddid
  • Start date Start date
S

sowetoddid

How can I make an IF statement to recognize only cells that are
"nonblank".

Usually I use SUMIF....based on a array = "rich burn". What would the
replacement for "rich burn" be??


Can I put in "nonblank"??


Thanks.
 
You can use LEN(cell) ie:

=IF(LEN(A1)>0,B1,"")

For the SUMIF, you'll need to use a sumproduct

=SUMPRODUCT((LEN(A1:A10)>0)*(B1:B10))

The above example will sum the B1:B10 range for all non-blank
entries in A1:A10.

Dan E
 
sowetoddid said:
How can I make an IF statement to recognize only cells that are
"nonblank".

Usually I use SUMIF....based on a array = "rich burn". What would the
replacement for "rich burn" be??

Can I put in "nonblank"??

Thanks.
Click to expand...

If you are summing cells (you mention SUMIF), you don't need to exclude
blank cells (as their value is zero). For other cases, you can use the
ISBLANK function or the test <>"" , for example
=SUMPRODUCT((A1:A10<>"")*................)
 
How can I make an IF statement to recognize only cells that are
"nonblank".

Usually I use SUMIF....based on a array = "rich burn". What would the
replacement for "rich burn" be??


Can I put in "nonblank"??


Thanks.
 
Back
Top