Finding a number
 

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.

Finding a number
 

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.

Finding a number
 

=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.

Finding a number
 

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.

Finding a number
 

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.

Finding a number
 

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.

Finding a number
 

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