RANGE EXCEL copy cell that meets criteria in a range
 

on a data sheet all data is imputed - sheet 1
I have a range of cells ie a7 to a2028 with dates in them
next range of cells b7 to b2028 has either the letter s or c in them
next range of cells c7 to c2028 has numbers in them

I want to have another sheet (sheet 2) that takes information from todays
date and either s or c in column b

ie if a1 to a2028 equals march 26,2008 and b1 to b2028 equals c then take
the value in column c that is in the same row as the criteria met above

I tried using the below formula but get 0 for a response

=IF(AND('Ser Data'!A7:A2120=TODAY(),'Ser Data'!B7:B2120="s"),'Ser
Data'!C7:C2120,0)

Please help

RANGE EXCEL copy cell that meets criteria in a range
 

If the values in column C are numeric values, and there will only be one
match, then you could use a SUMPRODUCT.
=SUMPRODUCT(--('Ser Data'!A7:A2028=TODAY()),--('Ser Data'!B7:B2028="s"),'Ser
Data'!C7:C2028)

or if there will be multiple matches and you want to sum the numeric values
in column that meet the criteria, then the above formula will work for that
as well.

Else, I would suspect a Pivot Table might be helpful.

HTH,
Paul


--

"confused" wrote in message
...
on a data sheet all data is imputed - sheet 1
I have a range of cells ie a7 to a2028 with dates in them
next range of cells b7 to b2028 has either the letter s or c in them
next range of cells c7 to c2028 has numbers in them

I want to have another sheet (sheet 2) that takes information from todays
date and either s or c in column b

ie if a1 to a2028 equals march 26,2008 and b1 to b2028 equals c then take
the value in column c that is in the same row as the criteria met above

I tried using the below formula but get 0 for a response

=IF(AND('Ser Data'!A7:A2120=TODAY(),'Ser Data'!B7:B2120="s"),'Ser
Data'!C7:C2120,0)

Please help

RANGE EXCEL copy cell that meets criteria in a range
 

That formula works perfectly. Thank you very much

Confused

"PCLIVE" wrote:

If the values in column C are numeric values, and there will only be one
match, then you could use a SUMPRODUCT.
=SUMPRODUCT(--('Ser Data'!A7:A2028=TODAY()),--('Ser Data'!B7:B2028="s"),'Ser
Data'!C7:C2028)

or if there will be multiple matches and you want to sum the numeric values
in column that meet the criteria, then the above formula will work for that
as well.

Else, I would suspect a Pivot Table might be helpful.

HTH,
Paul


--

"confused" wrote in message
...
on a data sheet all data is imputed - sheet 1
I have a range of cells ie a7 to a2028 with dates in them
next range of cells b7 to b2028 has either the letter s or c in them
next range of cells c7 to c2028 has numbers in them

I want to have another sheet (sheet 2) that takes information from todays
date and either s or c in column b

ie if a1 to a2028 equals march 26,2008 and b1 to b2028 equals c then take
the value in column c that is in the same row as the criteria met above

I tried using the below formula but get 0 for a response

=IF(AND('Ser Data'!A7:A2120=TODAY(),'Ser Data'!B7:B2120="s"),'Ser
Data'!C7:C2120,0)

Please help

RANGE EXCEL copy cell that meets criteria in a range
 

You're welcome.

--

"confused" wrote in message
...
That formula works perfectly. Thank you very much

Confused

"PCLIVE" wrote:

If the values in column C are numeric values, and there will only be one
match, then you could use a SUMPRODUCT.
=SUMPRODUCT(--('Ser Data'!A7:A2028=TODAY()),--('Ser
Data'!B7:B2028="s"),'Ser
Data'!C7:C2028)

or if there will be multiple matches and you want to sum the numeric
values
in column that meet the criteria, then the above formula will work for
that
as well.

Else, I would suspect a Pivot Table might be helpful.

HTH,
Paul


--

"confused" wrote in message
...
on a data sheet all data is imputed - sheet 1
I have a range of cells ie a7 to a2028 with dates in them
next range of cells b7 to b2028 has either the letter s or c in them
next range of cells c7 to c2028 has numbers in them

I want to have another sheet (sheet 2) that takes information from
todays
date and either s or c in column b

ie if a1 to a2028 equals march 26,2008 and b1 to b2028 equals c then
take
the value in column c that is in the same row as the criteria met above

I tried using the below formula but get 0 for a response

=IF(AND('Ser Data'!A7:A2120=TODAY(),'Ser Data'!B7:B2120="s"),'Ser
Data'!C7:C2120,0)

Please help