To Sum or not sum w/ blank cells
Author |
Message |
Kevin Weave #1 / 8
|
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 |
|
|
Dave Peterso #2 / 8
|
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 |
|
|
BobUmla #3 / 8
|
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 |
|
|
Jeff Polucc #4 / 8
|
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 |
|
|
Debra Dalgleis #5 / 8
|
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 |
|
|
Dave Peterso #6 / 8
|
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 |
|
|
Debra Dalgleis #7 / 8
|
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 |
|
|
Jeff Polucc #8 / 8
|
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 |
|
|
|