Sum all numeric values in this formula

[Guest]

I have the following formula:

=SUM(E45,H45,K45,N45,Q45,T45,W45,Z45,AC45,AF45)

I can't make it a Sum(E45:AF45) because other values are present.

I've used something like Sum(if(isnumber(E45:H45),E45:H45)), but it doesn't
seem to work when I have (E45,H45,K45) in the range.

Can someone assist?

Thanks,
Barb Reinhardt

 

[Don Guillett]

When you say "other values are present", what do you mean

e45 1a = not summed
e45 1 = summed
e45 a =ignored

 

[Guest]

E45 summed
F45 not summed
G45 not summed
H45 summed, etc.

 

[Don Guillett]

Barb,
What's in the cells? That's what my example was talking about.

 

[Guest]

The cells either contain a numeric value or NA(). They won't sum if the
value is NA(). I've gotten around this in the past with using
Sum(if(isnumber(), ... )

 

[Guest]

=SUMIF(E45:AF45,"<>#N/A",E45:AF45)

 

[Dave Peterson]

I like to use a different row with some kind of indicator in it (x's) to know
which column to sum.

=SUM(IF(ISNUMBER(E45:AF45),E45:AF45)*($E$44:$AF$44="x"))
(hit ctrl-shift-enter since it is an array formula)

I like this, since I can insert/delete columns without having to worry about
breaking my formula.


But if you want, you could check the column (your data is in every 4th column),
too:

=SUM(IF(ISNUMBER(E45:AF45),E45:AF45)
*(MOD(COLUMN(E45:AF45),3)=MOD(COLUMN(E45),3)))

(still array entered)

====
I learned my lesson when I created a formula like this (using the column, not
indicator) and shared it with a coworker. When he added some columns, the
formula broke--er, it still worked, but didn't do what he wanted.

 

[Bernd]

Hello,

As far as you do not have error values in your range:
=SUMPRODUCT(E45:AF45,--(MOD(COLUMN(E45:AF45),3)=2))

Regards,
Bernd

 

[Guest]

This is what I came up with that works.

=SUM(IF(MOD(COLUMN(E45:AH45),3)=2,IF(ISNUMBER(E45:AH45),E45:AH45)))