First time in arrays : no success

  • Thread starter Thread starter Gilbert De Ceulaer
  • Start date Start date
G

Gilbert De Ceulaer

I have an array A1:AE38 in sheet1
in sheet2 I would like to have the sum of the values sitting in column Q to
AE of that array
from the row that is looked up in column C of that array with the value
sitting in C16

{=LOOKUP( A16 ; 'sheet1'!C8:C38 ; SUM('sheet1'!Q8:AE38))}
gives me "#N/A"

What am I doing wrong ?
Gilbert
 
Here is one way


=SUM(OFFSET(Sheet1!Q8,MATCH(A16,Sheet1!C8:C38,0)-1,,,COLUMNS(Sheet1!Q:AE)))

if you want to dodge errors

=IF(COUNTIF(Sheet1!C8:C38,A16),SUM(OFFSET(Sheet1!Q8,MATCH(A16,Sheet1!C8:C38,
0)-1,,,COLUMNS(Sheet1!Q:AE))),"")
 
Back
Top