To Sum or not sum w/ blank cells 
Author Message
 To Sum or not sum w/ blank cells

You don't have to go to all that trouble. SUM ignores text when it sums.
Quote:

> HI,
> I have a row that contains 18 columns of numbers that I want to sum ONLY if each
> cell contains a number (no blanks or text)
> I've tried ISNUMBER without success. The following formula still sums what is
> there even though some columns are blank

> =IF(ISNUMBER(B3:S3),SUM(B3:S2),"NO TOTAL")

> If you can help, I would appreciate it.
> Sincerely,
> Jeff



Wed, 08 Oct 2003 08:00:16 GMT  
 To Sum or not sum w/ blank cells
how about

        =if(count(b3:s3)<>18,sum(b3:s3),"no total")

COUNT counts the number of cells with numbers.

(Use CountA text/numbers/formulas)

Quote:

> HI,
> I have a row that contains 18 columns of numbers that I want to sum ONLY if each
> cell contains a number (no blanks or text)
> I've tried ISNUMBER without success. The following formula still sums what is
> there even though some columns are blank

> =IF(ISNUMBER(B3:S3),SUM(B3:S2),"NO TOTAL")

> If you can help, I would appreciate it.
> Sincerely,
> Jeff

--

Dave Peterson



Wed, 08 Oct 2003 08:21:30 GMT  
 To Sum or not sum w/ blank cells
=IF(OR(NOT(ISNUMBER(B3:S2))),"NO TOTAL",SUM(B3:S2))

Must be array-entered (ctrl/shift/enter)


HI,
I have a row that contains 18 columns of numbers that I want to sum ONLY if
each
cell contains a number (no blanks or text)
I've tried ISNUMBER without success. The following formula still sums what
is
there even though some columns are blank

=IF(ISNUMBER(B3:S3),SUM(B3:S2),"NO TOTAL")

If you can help, I would appreciate it.
Sincerely,
Jeff



Wed, 08 Oct 2003 12:13:44 GMT  
 To Sum or not sum w/ blank cells
Kevin,
Didn't get my message explained properly. If a cell in the row is blank (or text)
I do NOT want to Sum the row. Only if all 18 cells contain numbers do I want a
sum. Otherwise I want a message like "Incomplete Entries" or somesuch.
Jeff
Quote:
-----Original Message-----
You don't have to go to all that trouble. SUM ignores text when it sums.


> HI,
> I have a row that contains 18 columns of numbers that I want to sum ONLY if each
> cell contains a number (no blanks or text)
> I've tried ISNUMBER without success. The following formula still sums what is
> there even though some columns are blank

> =IF(ISNUMBER(B3:S3),SUM(B3:S2),"NO TOTAL")

> If you can help, I would appreciate it.
> Sincerely,
> Jeff

.



Thu, 09 Oct 2003 21:28:01 GMT  
 To Sum or not sum w/ blank cells
Replace <> with = and it will work:

=IF(COUNT(B3:S3)=18,SUM(B3:S3),"no total")

Quote:

> how about

>         =if(count(b3:s3)<>18,sum(b3:s3),"no total")

> COUNT counts the number of cells with numbers.

> (Use CountA text/numbers/formulas)


> > HI,
> > I have a row that contains 18 columns of numbers that I want to sum ONLY if each
> > cell contains a number (no blanks or text)
> > I've tried ISNUMBER without success. The following formula still sums what is
> > there even though some columns are blank

> > =IF(ISNUMBER(B3:S3),SUM(B3:S2),"NO TOTAL")

> > If you can help, I would appreciate it.
> > Sincerely,
> > Jeff

> --

> Dave Peterson




Thu, 09 Oct 2003 21:59:00 GMT  
 To Sum or not sum w/ blank cells
oops.

Thanks for the correction.

What I may have meant <g> was this:

   =if(count(b3:s3)<>18,"no total",sum(b3:s3))

(But who knows!)

Quote:

> Replace <> with = and it will work:

> =IF(COUNT(B3:S3)=18,SUM(B3:S3),"no total")


> > how about

> >         =if(count(b3:s3)<>18,sum(b3:s3),"no total")

> > COUNT counts the number of cells with numbers.

> > (Use CountA text/numbers/formulas)


> > > HI,
> > > I have a row that contains 18 columns of numbers that I want to sum ONLY if each
> > > cell contains a number (no blanks or text)
> > > I've tried ISNUMBER without success. The following formula still sums what is
> > > there even though some columns are blank

> > > =IF(ISNUMBER(B3:S3),SUM(B3:S2),"NO TOTAL")

> > > If you can help, I would appreciate it.
> > > Sincerely,
> > > Jeff

> > --

> > Dave Peterson


--

Dave Peterson



Fri, 10 Oct 2003 06:48:01 GMT  
 To Sum or not sum w/ blank cells
It was Friday. It was late.
It's impressive that you could type _anything_ under those
circumstances! :-)
Quote:

> oops.

> Thanks for the correction.

> What I may have meant <g> was this:

>    =if(count(b3:s3)<>18,"no total",sum(b3:s3))

> (But who knows!)


> > Replace <> with = and it will work:

> > =IF(COUNT(B3:S3)=18,SUM(B3:S3),"no total")


> > > how about

> > >         =if(count(b3:s3)<>18,sum(b3:s3),"no total")

> > > COUNT counts the number of cells with numbers.

> > > (Use CountA text/numbers/formulas)


> > > > HI,
> > > > I have a row that contains 18 columns of numbers that I want to sum ONLY if each
> > > > cell contains a number (no blanks or text)
> > > > I've tried ISNUMBER without success. The following formula still sums what is
> > > > there even though some columns are blank

> > > > =IF(ISNUMBER(B3:S3),SUM(B3:S2),"NO TOTAL")

> > > > If you can help, I would appreciate it.
> > > > Sincerely,
> > > > Jeff

> > > --

> > > Dave Peterson

> --

> Dave Peterson




Fri, 10 Oct 2003 07:42:47 GMT  
 To Sum or not sum w/ blank cells
The formula
=IF(COUNT(B3:S3)<>18,SUM(B3:S3),"NO TOTAL")
worked nearly perfectly. I had to change <> to = because I only wanted a sum if
ALL 18 cells contained a number.

This suggestion also worked perfectly(although I have a headache trying to track
the if, is,or,not,is equation (big smile)
=IF(OR(NOT(ISNUMBER(B3:S3))),"NO TOTAL",SUM(B3:S3))

Thanks for all your help.

Quote:
-----Original Message-----

It was Friday. It was late.
It's impressive that you could type _anything_ under those
circumstances! :-)


> oops.

> Thanks for the correction.

> What I may have meant <g> was this:

>    =if(count(b3:s3)<>18,"no total",sum(b3:s3))

> (But who knows!)


> > Replace <> with = and it will work:

> > =IF(COUNT(B3:S3)=18,SUM(B3:S3),"no total")


> > > how about

> > >         =if(count(b3:s3)<>18,sum(b3:s3),"no total")

> > > COUNT counts the number of cells with numbers.

> > > (Use CountA text/numbers/formulas)


> > > > HI,
> > > > I have a row that contains 18 columns of numbers that I want to sum ONLY
if each
> > > > cell contains a number (no blanks or text)
> > > > I've tried ISNUMBER without success. The following formula still sums
what is
> > > > there even though some columns are blank

> > > > =IF(ISNUMBER(B3:S3),SUM(B3:S2),"NO TOTAL")

> > > > If you can help, I would appreciate it.
> > > > Sincerely,
> > > > Jeff

> > > --

> > > Dave Peterson

> --

> Dave Peterson

.



Fri, 10 Oct 2003 08:21:32 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. SUM, If a cell is blank do not SUM the Range

2. Sum cells if not blank

3. Summing using formulas already in cells, not cell references, or values in cells

4. Sum() - Does Not Sum

5. SUM not summing all requested rows

6. Réf. : sum and sum not

7. sum and sum not

8. Preview Sum and Printed Sum not same

9. summing non-blank cells

10. summing last 12 non-blank cells in range


 
Powered by phpBB® Forum Software © phpBB Group