'Count/Sum a column but exclude #N/A
Currently using the following forumlas to count the number of records in all of column Z that does not have #N/A but it does not work. All the rows in Column Z have a formula itself (Which is why some of them display #N/A, its a VLOOKUP).
=COUNTA(Z:Z)-SUM(IF(ISNA(Z:Z),1))
=SUMPRODUCT(--(TRIM(Z:Z)<>"#N/A"))
These return a "0" value which is not true, what am I doing incorrect?
Solution 1:[1]
If you are using Excel 2010 or later, to count non-error values you can use (regular formula)
=AGGREGATE(3,6,Z:Z)
Solution 2:[2]
No reason to use an array formula for this, you can just do something like
=COUNTIFS(Z:Z, "<>#N/A",Z:Z, "<>")
or
=COUNTA(Z:Z) - COUNTIF(Z:Z,"=#N/A")
The first one counts every nonblank, non #N/A cell. The second does what you're trying to do now and subtracts the total of #N/A cells from the total of every nonblank cell. Maybe using ISNA is technically more correct or faster, but this probably works just as well for most cases.
Solution 3:[3]
This array formula sums the cells of range Z:Z that are not NA's :
=SUM(IF(NOT(ISNA(Z:Z)),Z:Z)) Ctrl+Shift+Enter
This one (which is probably what you want) sums all but errors:
=SUM(IF(NOT(ISERROR(Z:Z)),Z:Z)) Ctrl+Shift+Enter
And another (simpler) one
=SUM(IFERROR(H:H, 0)) Ctrl+Shift+Enter
Solution 4:[4]
Are you entering it as an array formula? Press Ctrl-Shift-Enter instead of just enter. I think the first formula should work.
=COUNTA(Z:Z)-SUM(IF(ISNA(Z:Z),1))
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | chris neilsen |
| Solution 2 | Wedge |
| Solution 3 | |
| Solution 4 | Andrew |