Forum Discussion
SUMIF criteria
I am trying to sum then average a list of numbers. e.g.
| 5 | - | 12 | - | - | 34 |
=SUMif(D2:AP2,D2:AP2,isnumber()) |
I need to add all the numbers, ignoring the - and empty cells, then average over the total number of cells in the selection.
I cannot get Excel to add up the numbers.
How do I do that first stage, i.e. ignoring the non-numerical cell contents?
Hi,
In this case, you don't need to SUMIF function, SUM function is sufficient, it's programmed to ignore texts and empty cells, and AVERAGE function as well.
- Sorry. I had not realised that the result was placed adjacent to the formula. So the formula did not compute at all. I thought that the formula was calculated in situ. Has this property changed recently?
Thank youHi,
That property isn't changed!
The formula is calculated in situ!
In the screenshot above I've used FORMULATEXT Function that returns what is displayed in the formula bar!
I have put it in cells: H3, I3, D8, and E8.
But I have deleted it before I upload the attached file to you; it may not be available with your copy of Excel.
