RANGE EXCEL copy cell that meets criteria in a range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

confused - 26 Mar 2008 14:04 GMT

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

Reply to this Message

PCLIVE - 26 Mar 2008 14:52 GMT

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

> on a data sheet all data is imputed - sheet 1
> I have a range of cells ie a7 to a2028 with dates in them
[quoted text clipped - 13 lines]
>
> Please help

Reply to this Message

confused - 27 Mar 2008 00:06 GMT

That formula works perfectly. Thank you very much

Confused

> If the values in column C are numeric values, and there will only be one
> match, then you could use a SUMPRODUCT.
[quoted text clipped - 27 lines]
> >
> > Please help

Reply to this Message

PCLIVE - 27 Mar 2008 14:41 GMT

You're welcome.

> That formula works perfectly. Thank you very much
>
[quoted text clipped - 36 lines]
>> >
>> > Please help

Reply to this Message