Sum all numeric values in this formula
 

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

Sum all numeric values in this formula
 

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

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

--
Don Guillett
SalesAid Software

"Barb Reinhardt" wrote in message
...
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

Sum all numeric values in this formula
 

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

"Don Guillett" wrote:

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

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

--
Don Guillett
SalesAid Software

"Barb Reinhardt" wrote in message
...
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

Sum all numeric values in this formula
 

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

--
Don Guillett
SalesAid Software

"Barb Reinhardt" wrote in message
...
E45 summed
F45 not summed
G45 not summed
H45 summed, etc.

"Don Guillett" wrote:

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

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

--
Don Guillett
SalesAid Software

"Barb Reinhardt" wrote in
message
...
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

Sum all numeric values in this formula
 

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(), ... )


"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"Barb Reinhardt" wrote in message
...
E45 summed
F45 not summed
G45 not summed
H45 summed, etc.

"Don Guillett" wrote:

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

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

--
Don Guillett
SalesAid Software

"Barb Reinhardt" wrote in
message
...
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

Sum all numeric values in this formula
 

=SUMIF(E45:AF45,"<#N/A",E45:AF45)
--
Gary''s Student
gsnu200711

Sum all numeric values in this formula
 

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.



Barb Reinhardt wrote:

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

--

Dave Peterson

Sum all numeric values in this formula
 

Hello,

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

Regards,
Bernd

Sum all numeric values in this formula
 

This is what I came up with that works.

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

"Bernd" wrote:

Hello,

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

Regards,
Bernd