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

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

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

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

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

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

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

Hello,

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

Regards,
Bernd

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