| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count IF?
Excel 2007
I have a simple formula, =sum(F13:F38) I have added formulas in other cells that when nothing is in the cells, #N/A appears in some of the cells (F13 to F18) and my above formula will no longer work. Is there a way to correct this formula to just add up the cells with numbers? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count IF?
=SUMPRODUCT(--(ISNA(F13:F38)=FALSE),F13:F38) "Canon" wrote: Excel 2007 I have a simple formula, =sum(F13:F38) I have added formulas in other cells that when nothing is in the cells, #N/A appears in some of the cells (F13 to F18) and my above formula will no longer work. Is there a way to correct this formula to just add up the cells with numbers? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count IF?
Try one of these:
=SUMIF(F13:F38,"<1E100") =SUMIF(F13:F38,"<#N/A") -- Biff Microsoft Excel MVP "Canon" wrote in message ... Excel 2007 I have a simple formula, =sum(F13:F38) I have added formulas in other cells that when nothing is in the cells, #N/A appears in some of the cells (F13 to F18) and my above formula will no longer work. Is there a way to correct this formula to just add up the cells with numbers? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count IF?
=SUMPRODUCT(--(ISNA(F13:F38)=FALSE),F13:F38)
I don't think that works. -- Biff Microsoft Excel MVP "Joel" wrote in message ... =SUMPRODUCT(--(ISNA(F13:F38)=FALSE),F13:F38) "Canon" wrote: Excel 2007 I have a simple formula, =sum(F13:F38) I have added formulas in other cells that when nothing is in the cells, #N/A appears in some of the cells (F13 to F18) and my above formula will no longer work. Is there a way to correct this formula to just add up the cells with numbers? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count IF?
Why wouldn't it work?
"T. Valko" wrote: =SUMPRODUCT(--(ISNA(F13:F38)=FALSE),F13:F38) I don't think that works. -- Biff Microsoft Excel MVP "Joel" wrote in message ... =SUMPRODUCT(--(ISNA(F13:F38)=FALSE),F13:F38) "Canon" wrote: Excel 2007 I have a simple formula, =sum(F13:F38) I have added formulas in other cells that when nothing is in the cells, #N/A appears in some of the cells (F13 to F18) and my above formula will no longer work. Is there a way to correct this formula to just add up the cells with numbers? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count IF?
Try putting =NA() into one of the cells in the range... the formula
evaluates to #NA, probably because SUMPRODUCT attempts to multiply the #N/A by 0 for cells containing #N/A, which it cannot do. -- Rick (MVP - Excel) "Joel" wrote in message ... Why wouldn't it work? "T. Valko" wrote: =SUMPRODUCT(--(ISNA(F13:F38)=FALSE),F13:F38) I don't think that works. -- Biff Microsoft Excel MVP "Joel" wrote in message ... =SUMPRODUCT(--(ISNA(F13:F38)=FALSE),F13:F38) "Canon" wrote: Excel 2007 I have a simple formula, =sum(F13:F38) I have added formulas in other cells that when nothing is in the cells, #N/A appears in some of the cells (F13 to F18) and my above formula will no longer work. Is there a way to correct this formula to just add up the cells with numbers? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count IF?
Your right. 0 * N/A = N/A. I thought it would be 0.
"Rick Rothstein" wrote: Try putting =NA() into one of the cells in the range... the formula evaluates to #NA, probably because SUMPRODUCT attempts to multiply the #N/A by 0 for cells containing #N/A, which it cannot do. -- Rick (MVP - Excel) "Joel" wrote in message ... Why wouldn't it work? "T. Valko" wrote: =SUMPRODUCT(--(ISNA(F13:F38)=FALSE),F13:F38) I don't think that works. -- Biff Microsoft Excel MVP "Joel" wrote in message ... =SUMPRODUCT(--(ISNA(F13:F38)=FALSE),F13:F38) "Canon" wrote: Excel 2007 I have a simple formula, =sum(F13:F38) I have added formulas in other cells that when nothing is in the cells, #N/A appears in some of the cells (F13 to F18) and my above formula will no longer work. Is there a way to correct this formula to just add up the cells with numbers? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count IF?
Try the following array formula: =SUM(IF(ISNA(F13:F38),FALSE,F13:F38)) Since this is an Array Formula, you *must* press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the Formula Bar enclosed in curly braces { }. (You do not type the curly braces - Excel includes them automatically.) The formula will not work properly if you do not use CTRL SHIFT ENTER. See http://www.cpearson.com/excel/ArrayFormulas.aspx for lots more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 28 May 2009 10:05:01 -0700, Canon wrote: Excel 2007 I have a simple formula, =sum(F13:F38) I have added formulas in other cells that when nothing is in the cells, #N/A appears in some of the cells (F13 to F18) and my above formula will no longer work. Is there a way to correct this formula to just add up the cells with numbers? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
| Count Employee Work Time - Don't Count Duplicates | Excel Worksheet Functions | |||
| Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
| Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
