Return value in cell above the reference
 

I've used the following formula to return the information I want to cell
N1434:

=SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F1434),N$2:N$1404)

In this instance it returns the value from cell N773, which is
correct.

Now in Cell N1433 I would like to return the value in cell N772. I
can't use the sumproduct function because the references can't be used
again without throwing off other calculations. I tried to get the row
function to go out and return N773 as an answer to something but failed
miserably. Any help is appreciated.

Jean


--
Jean
------------------------------------------------------------------------
Jean's Profile: http://www.excelforum.com/member.php...o&userid=17585
View this thread: http://www.excelforum.com/showthread...hreadid=547093

Return value in cell above the reference
 

What if the returned value is that in cell N2?
Do you want the contents of cell N1 to be returned?

HTH
--
AP

"Jean" a écrit dans le
message de news: ...

I've used the following formula to return the information I want to cell
N1434:

=SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F1434),N$2:N$1404)

In this instance it returns the value from cell N773, which is
correct.

Now in Cell N1433 I would like to return the value in cell N772. I
can't use the sumproduct function because the references can't be used
again without throwing off other calculations. I tried to get the row
function to go out and return N773 as an answer to something but failed
miserably. Any help is appreciated.

Jean


--
Jean
------------------------------------------------------------------------
Jean's Profile:
http://www.excelforum.com/member.php...o&userid=17585
View this thread: http://www.excelforum.com/showthread...hreadid=547093

Return value in cell above the reference
 

You can use $N$1:$N$1403 as returned range:

=SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F1434),N$1:N$1403)


--
AP

"Jean" a écrit dans le
message de news: ...

I've used the following formula to return the information I want to cell
N1434:

=SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F1434),N$2:N$1404)

In this instance it returns the value from cell N773, which is
correct.

Now in Cell N1433 I would like to return the value in cell N772. I
can't use the sumproduct function because the references can't be used
again without throwing off other calculations. I tried to get the row
function to go out and return N773 as an answer to something but failed
miserably. Any help is appreciated.

Jean


--
Jean
------------------------------------------------------------------------
Jean's Profile:
http://www.excelforum.com/member.php...o&userid=17585
View this thread: http://www.excelforum.com/showthread...hreadid=547093

Return value in cell above the reference
 

Try...

=INDEX(N$2:N$1404,MATCH(1,IF($Y$2:$Y$1404=$Y1434,I F($E$2:$E$1404=$E1434,I
F($F$2:$F$1404=$F1434,1))),0)-1)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Jean wrote:

I've used the following formula to return the information I want to cell
N1434:

=SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F
1434),N$2:N$1404)

In this instance it returns the value from cell N773, which is
correct.

Now in Cell N1433 I would like to return the value in cell N772. I
can't use the sumproduct function because the references can't be used
again without throwing off other calculations. I tried to get the row
function to go out and return N773 as an answer to something but failed
miserably. Any help is appreciated.

Jean

Return value in cell above the reference
 

Both solutions worked! Thank you so much--you are the rock stars of the
excel universe!
Jean


--
Jean
------------------------------------------------------------------------
Jean's Profile: http://www.excelforum.com/member.php...o&userid=17585
View this thread: http://www.excelforum.com/showthread...hreadid=547093