Hi, I wonder if any one can help, I want to look for a number in a row of
cells, I have a row of twenty cells that have a number in each all different,
What I would like to do is search the row for a number between 39814 and
39844 and copy it into another cell, there will only be one number in this
range.
Can any one help, Thanks in advance, Sam.

One way...

A1:T1 = numbers

A3 = 39814
B3 = 39844

Is it just a coincidence that those are the serial date numbers for the
dates 1/1/2009 and 1/31/2009 ?

=SUMIF(A1:T1,"="&A3,A1:T1)-SUMIF(A1:T1,""&B3,A1:T1)

--
Biff
Microsoft Excel MVP


"Sam" wrote in message
...
Hi, I wonder if any one can help, I want to look for a number in a row of
cells, I have a row of twenty cells that have a number in each all
different,
What I would like to do is search the row for a number between 39814 and
39844 and copy it into another cell, there will only be one number in this
range.
Can any one help, Thanks in advance, Sam.

=SUMIF(A1:T1,"="&A3,A1:T1)-SUMIF(A1:T1,""&B3,A1:T1)

Even better!

=SUMIF(A1:T1,"="&A3)-SUMIF(A1:T1,""&B3)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

A1:T1 = numbers

A3 = 39814
B3 = 39844

Is it just a coincidence that those are the serial date numbers for the
dates 1/1/2009 and 1/31/2009 ?

=SUMIF(A1:T1,"="&A3,A1:T1)-SUMIF(A1:T1,""&B3,A1:T1)

--
Biff
Microsoft Excel MVP


"Sam" wrote in message
...
Hi, I wonder if any one can help, I want to look for a number in a row of
cells, I have a row of twenty cells that have a number in each all
different,
What I would like to do is search the row for a number between 39814 and
39844 and copy it into another cell, there will only be one number in
this
range.
Can any one help, Thanks in advance, Sam.

Here's one way, Sam

Assume your source range is A2:A20
Put this in the cell where you want the result,
say in B2, press normal ENTER to confirm:
=INDEX(A2:A20,MATCH(1,INDEX((A2:A2039814)*(A2:A20 <39844),),0))
B2 will return the required result

If you need an error trap to return neat looking blanks should there not be
a match, use IF(ISNA to trap the MATCH part of it, indicatively:
=IF(ISNA(MATCH(..)),"",INDEX(..))

viz, in B2:
=IF(ISNA(MATCH(1,INDEX((A2:A2039814)*(A2:A20<3984 4),),0)),"",INDEX(A2:A20,MATCH(1,INDEX((A2:A20398 14)*(A2:A20<39844),),0)))

Success? celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Sam" wrote:
Hi, I wonder if any one can help, I want to look for a number in a row of
cells, I have a row of twenty cells that have a number in each all different,
What I would like to do is search the row for a number between 39814 and
39844 and copy it into another cell, there will only be one number in this
range.
Can any one help, Thanks in advance, Sam.

HI, T thanks a will give that a try and yes they are dates, my dad said it
may be easier to convert them to numbers, Sam

"T. Valko" wrote:

=SUMIF(A1:T1,"="&A3,A1:T1)-SUMIF(A1:T1,""&B3,A1:T1)

Even better!

=SUMIF(A1:T1,"="&A3)-SUMIF(A1:T1,""&B3)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

A1:T1 = numbers

A3 = 39814
B3 = 39844

Is it just a coincidence that those are the serial date numbers for the
dates 1/1/2009 and 1/31/2009 ?

=SUMIF(A1:T1,"="&A3,A1:T1)-SUMIF(A1:T1,""&B3,A1:T1)

--
Biff
Microsoft Excel MVP


"Sam" wrote in message
...
Hi, I wonder if any one can help, I want to look for a number in a row of
cells, I have a row of twenty cells that have a number in each all
different,
What I would like to do is search the row for a number between 39814 and
39844 and copy it into another cell, there will only be one number in
this
range.
Can any one help, Thanks in advance, Sam.

Thank you Max, I will give that a go as well, Sam

"Max" wrote:

Here's one way, Sam

Assume your source range is A2:A20
Put this in the cell where you want the result,
say in B2, press normal ENTER to confirm:
=INDEX(A2:A20,MATCH(1,INDEX((A2:A2039814)*(A2:A20 <39844),),0))
B2 will return the required result

If you need an error trap to return neat looking blanks should there not be
a match, use IF(ISNA to trap the MATCH part of it, indicatively:
=IF(ISNA(MATCH(..)),"",INDEX(..))

viz, in B2:
=IF(ISNA(MATCH(1,INDEX((A2:A2039814)*(A2:A20<3984 4),),0)),"",INDEX(A2:A20,MATCH(1,INDEX((A2:A20398 14)*(A2:A20<39844),),0)))

Success? celebrate it, hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Sam" wrote:
Hi, I wonder if any one can help, I want to look for a number in a row of
cells, I have a row of twenty cells that have a number in each all different,
What I would like to do is search the row for a number between 39814 and
39844 and copy it into another cell, there will only be one number in this
range.
Can any one help, Thanks in advance, Sam.

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Sam" wrote in message
...
Thank you Max, I will give that a go as well, Sam