PDA

View Full Version : Dealing with #N/A and non-continuous sums



energizek
11-30-2009, 06:51 AM
Hi, I am making a budget and need to sum certain cells that contain numbers. However, if the user doesn't have an item for a certain category that cell will read "#N/A". When it comes time to get a total Excel won't sum the cells unless all of them have a numeric value. I've tried using "isnumber" and "sumif" but get an error saying that I've entered "too many arguments for the function."

Here is my code:
=If(Isnumber(AA173,Z173,Y173,W173,U173,R173,P173,J173,H173,F173), Sum(AA173,Z173,Y173,W173,U173,R173,P173,J173,H173,F173),0)

Any suggestions?

-Katie

mikerickson
11-30-2009, 08:00 AM
This is one way to test if all the entries in a discontinous range are numbers or blanks.
=IF(ISNUMBER(VALUE(C5&C7&B10&B12)),"all numbers or blanks","not")

This won't work unless the numbers in those cells are all non-negative
integers.

Bob Phillips
11-30-2009, 08:31 AM
Try this array formula

=SUM(IF(ISNUMBER(N(OFFSET(F173:AA173,0,{0,2,4,10,12,15,17,19,20,21},1,1))), N(OFFSET(F173:AA173,0,{0,2,4,10,12,15,17,19,20,21},1,1))))