HI all, I have six cells; c9,c17,c25,j9,j17,j25

if the number entered in any of these cells is over 39 then I want to total
the number over that amount. is that clear? for example-

cell c9 has 30
cell c17 has 39
cell c25 has 40
cell j9 has 23
cell j17 has 42
cell j25 has 2

the answer would be 3

Any help gratefully received! (again)

Steve

First, how are you determining the # is three. I see one cell with a value
greater than 39.

"R.P.McMurphy" wrote in message
...
HI all, I have six cells; c9,c17,c25,j9,j17,j25

if the number entered in any of these cells is over 39 then I want to
total the number over that amount. is that clear? for example-

cell c9 has 30
cell c17 has 39
cell c25 has 40
cell j9 has 23
cell j17 has 42
cell j25 has 2

the answer would be 3

Any help gratefully received! (again)

Steve

Correction ... it's still early here ... when you say over 39 do you really
mean = 39?

"R.P.McMurphy" wrote in message
...
HI all, I have six cells; c9,c17,c25,j9,j17,j25

if the number entered in any of these cells is over 39 then I want to
total the number over that amount. is that clear? for example-

cell c9 has 30
cell c17 has 39
cell c25 has 40
cell j9 has 23
cell j17 has 42
cell j25 has 2

the answer would be 3

Any help gratefully received! (again)

Steve

i.e. 39=0 whereas 40=1 i.e. is 1 over 39.

cheers!

Steve

Correction ... it's still early here ... when you say over 39 do you
really mean = 39?

"R.P.McMurphy" wrote in message
...
HI all, I have six cells; c9,c17,c25,j9,j17,j25

if the number entered in any of these cells is over 39 then I want to
total the number over that amount. is that clear? for example-

cell c9 has 30
cell c17 has 39
cell c25 has 40
cell j9 has 23
cell j17 has 42
cell j25 has 2

the answer would be 3

Any help gratefully received! (again)

Steve

I can get it to partially work with this

=COUNTIF(C9:I25,"=39")

But am unable to put a range in cell by cell. Maybe someone else can assist
here.

"R.P.McMurphy" wrote in message
...
HI all, I have six cells; c9,c17,c25,j9,j17,j25

if the number entered in any of these cells is over 39 then I want to
total the number over that amount. is that clear? for example-

cell c9 has 30
cell c17 has 39
cell c25 has 40
cell j9 has 23
cell j17 has 42
cell j25 has 2

the answer would be 3

Any help gratefully received! (again)

Steve

Hi Steve,
39=0 OK
40=1 OK
42=3 OK?
Now, 1 + 3 = 4 OK?
How come you get 3?

My (yukky) formula is:

=(SUM(C9,C17,C25,J9,J17,J25) - 6*39 + ABS(C9-39) + ABS(C17-39) +
ABS(C25-39)+ ABS(J9-39) + ABS(J17-39) + ABS(J25-39))/2

which gives the result 4 with the values supplied.

Ken Johnson

hehe...you spotted the deliberate mistake!

many thanks all!

steve

"Ken Johnson" wrote in message
oups.com...
Hi Steve,
39=0 OK
40=1 OK
42=3 OK?
Now, 1 + 3 = 4 OK?
How come you get 3?

My (yukky) formula is:

=(SUM(C9,C17,C25,J9,J17,J25) - 6*39 + ABS(C9-39) + ABS(C17-39) +
ABS(C25-39)+ ABS(J9-39) + ABS(J17-39) + ABS(J25-39))/2

which gives the result 4 with the values supplied.

Ken Johnson

How about

=SUMPRODUCT(--(N(OFFSET(C9:C25,{0,8,16},0,1,1))=39))+SUMPRODUCT (--(N(OFFSET
(J9:J25,{0,8,16},0,1,1))=39))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ken Johnson" wrote in message
oups.com...
Hi Steve,
39=0 OK
40=1 OK
42=3 OK?
Now, 1 + 3 = 4 OK?
How come you get 3?

My (yukky) formula is:

=(SUM(C9,C17,C25,J9,J17,J25) - 6*39 + ABS(C9-39) + ABS(C17-39) +
ABS(C25-39)+ ABS(J9-39) + ABS(J17-39) + ABS(J25-39))/2

which gives the result 4 with the values supplied.

Ken Johnson

I have changed it to

=SUMPRODUCT(--(N(OFFSET(table1 C9:table1
C25,{0,8,16},0,1,1))=39))+SUMPRODUCT(--(N(OFFSET(table1 J9:table1
J25,{0,8,16},0,1,1))=39))

but its still not working,

help!

Steve
"Bob Phillips" wrote in message
...
How about

=SUMPRODUCT(--(N(OFFSET(C9:C25,{0,8,16},0,1,1))=39))+SUMPRODUCT (--(N(OFFSET
(J9:J25,{0,8,16},0,1,1))=39))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ken Johnson" wrote in message
oups.com...
Hi Steve,
39=0 OK
40=1 OK
42=3 OK?
Now, 1 + 3 = 4 OK?
How come you get 3?

My (yukky) formula is:

=(SUM(C9,C17,C25,J9,J17,J25) - 6*39 + ABS(C9-39) + ABS(C17-39) +
ABS(C25-39)+ ABS(J9-39) + ABS(J17-39) + ABS(J25-39))/2

which gives the result 4 with the values supplied.

Ken Johnson

I put 'table1' in fornt of all the c9,c17,c25 j9 s... ect, but all I am
getting is 9, no mater what I enter into the fields.

Steve


"Ken Johnson" wrote in message
oups.com...
Hi Steve,
39=0 OK
40=1 OK
42=3 OK?
Now, 1 + 3 = 4 OK?
How come you get 3?

My (yukky) formula is:

=(SUM(C9,C17,C25,J9,J17,J25) - 6*39 + ABS(C9-39) + ABS(C17-39) +
ABS(C25-39)+ ABS(J9-39) + ABS(J17-39) + ABS(J25-39))/2

which gives the result 4 with the values supplied.

Ken Johnson

Try

=SUMPRODUCT(--(N(OFFSET('table1'!C9:C25,{0,8,16},0,1,1))=39))+S UMPRODUCT(--
(N(OFFSET('table1'!J9:J25,{0,8,16},0,1,1))=39))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"R.P.McMurphy" wrote in message
...
I have changed it to

=SUMPRODUCT(--(N(OFFSET(table1 C9:table1
C25,{0,8,16},0,1,1))=39))+SUMPRODUCT(--(N(OFFSET(table1 J9:table1
J25,{0,8,16},0,1,1))=39))

but its still not working,

help!

Steve
"Bob Phillips" wrote in message
...
How about


=SUMPRODUCT(--(N(OFFSET(C9:C25,{0,8,16},0,1,1))=39))+SUMPRODUCT (--(N(OFFSET
(J9:J25,{0,8,16},0,1,1))=39))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ken Johnson" wrote in message
oups.com...
Hi Steve,
39=0 OK
40=1 OK
42=3 OK?
Now, 1 + 3 = 4 OK?
How come you get 3?

My (yukky) formula is:

=(SUM(C9,C17,C25,J9,J17,J25) - 6*39 + ABS(C9-39) + ABS(C17-39) +
ABS(C25-39)+ ABS(J9-39) + ABS(J17-39) + ABS(J25-39))/2

which gives the result 4 with the values supplied.

Ken Johnson

no its still not working. I am not even getting a result.

Steve

"Bob Phillips" wrote in message
...
Try

=SUMPRODUCT(--(N(OFFSET('table1'!C9:C25,{0,8,16},0,1,1))=39))+S UMPRODUCT(--
(N(OFFSET('table1'!J9:J25,{0,8,16},0,1,1))=39))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"R.P.McMurphy" wrote in message
...
I have changed it to

=SUMPRODUCT(--(N(OFFSET(table1 C9:table1
C25,{0,8,16},0,1,1))=39))+SUMPRODUCT(--(N(OFFSET(table1 J9:table1
J25,{0,8,16},0,1,1))=39))

but its still not working,

help!

Steve
"Bob Phillips" wrote in message
...
How about


=SUMPRODUCT(--(N(OFFSET(C9:C25,{0,8,16},0,1,1))=39))+SUMPRODUCT (--(N(OFFSET
(J9:J25,{0,8,16},0,1,1))=39))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ken Johnson" wrote in message
oups.com...
Hi Steve,
39=0 OK
40=1 OK
42=3 OK?
Now, 1 + 3 = 4 OK?
How come you get 3?

My (yukky) formula is:

=(SUM(C9,C17,C25,J9,J17,J25) - 6*39 + ABS(C9-39) + ABS(C17-39) +
ABS(C25-39)+ ABS(J9-39) + ABS(J17-39) + ABS(J25-39))/2

which gives the result 4 with the values supplied.

Ken Johnson

working with this one Bob, I just open a new spread sheet up and entered the
figures and your original formula. it does indeed work for the figures
quoted. but if I change c25 to 41 the result doesn't change, it should go
up by one i.e. 4. like wise, if I changed j17 to 40 the result should be 1.

Steve

"Bob Phillips" wrote in message
...
How about

=SUMPRODUCT(--(N(OFFSET(C9:C25,{0,8,16},0,1,1))=39))+SUMPRODUCT (--(N(OFFSET
(J9:J25,{0,8,16},0,1,1))=39))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ken Johnson" wrote in message
oups.com...
Hi Steve,
39=0 OK
40=1 OK
42=3 OK?
Now, 1 + 3 = 4 OK?
How come you get 3?

My (yukky) formula is:

=(SUM(C9,C17,C25,J9,J17,J25) - 6*39 + ABS(C9-39) + ABS(C17-39) +
ABS(C25-39)+ ABS(J9-39) + ABS(J17-39) + ABS(J25-39))/2

which gives the result 4 with the values supplied.

Ken Johnson

Ken, yours is working well. my problem is I am using this formula in MS
Word in a table to be used as a form. the answer goes in table 2 and the
reference cells go in table 1. how would one change it to refer to the
cells in table1? the formula as you have written it works great if used in
the same table as the reference cells.

cheers

Steve

"Ken Johnson" wrote in message
oups.com...
Hi Steve,
39=0 OK
40=1 OK
42=3 OK?
Now, 1 + 3 = 4 OK?
How come you get 3?

My (yukky) formula is:

=(SUM(C9,C17,C25,J9,J17,J25) - 6*39 + ABS(C9-39) + ABS(C17-39) +
ABS(C25-39)+ ABS(J9-39) + ABS(J17-39) + ABS(J25-39))/2

which gives the result 4 with the values supplied.

Ken Johnson

It changes for me.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"R.P.McMurphy" wrote in message
...
working with this one Bob, I just open a new spread sheet up and entered
the
figures and your original formula. it does indeed work for the figures
quoted. but if I change c25 to 41 the result doesn't change, it should go
up by one i.e. 4. like wise, if I changed j17 to 40 the result should be
1.

Steve

"Bob Phillips" wrote in message
...
How about


=SUMPRODUCT(--(N(OFFSET(C9:C25,{0,8,16},0,1,1))=39))+SUMPRODUCT (--(N(OFFSET
(J9:J25,{0,8,16},0,1,1))=39))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ken Johnson" wrote in message
oups.com...
Hi Steve,
39=0 OK
40=1 OK
42=3 OK?
Now, 1 + 3 = 4 OK?
How come you get 3?

My (yukky) formula is:

=(SUM(C9,C17,C25,J9,J17,J25) - 6*39 + ABS(C9-39) + ABS(C17-39) +
ABS(C25-39)+ ABS(J9-39) + ABS(J17-39) + ABS(J25-39))/2

which gives the result 4 with the values supplied.

Ken Johnson

Hi Steve,
Sorry Steve, I had to get to bed, it was the wee hours of the morning
in Sydney.
I don't know much about Word and what you're trying to do.
Maybe the fact that you're in Word explains why Bob's SUMPRODUCT
formula didn't work.
Word's math ability is much much less than Excel's and it was just
fortuitous that my formula only contains SUM and ABS, which Word
probably has.
All I can suggest is that you post your problem in one of the Word
groups, such as microsoft.public.word.docmanagement.
Sorry I can't do more than that, but I will be thinking about this
problem of linking Word table values and will let you know if I have
any success.
Ken Johnson

Thanks for that Ken, will try over there!

Steve

"Ken Johnson" wrote in message
oups.com...
Hi Steve,
Sorry Steve, I had to get to bed, it was the wee hours of the morning
in Sydney.
I don't know much about Word and what you're trying to do.
Maybe the fact that you're in Word explains why Bob's SUMPRODUCT
formula didn't work.
Word's math ability is much much less than Excel's and it was just
fortuitous that my formula only contains SUM and ABS, which Word
probably has.
All I can suggest is that you post your problem in one of the Word
groups, such as microsoft.public.word.docmanagement.
Sorry I can't do more than that, but I will be thinking about this
problem of linking Word table values and will let you know if I have
any success.
Ken Johnson