EXCEL FORMULA , MEET 2 CRITERIA THEN SUM

[Elaine]

I would like column H to be searched to find xxxx, then when that is found, I
would like column J same row to be searched to find xxxxxxxxxxx, then I would
like to place the amount found in column L five rows below the finding into
another excel spreadsheet.

ie. H10 XXXXX, J10 XXXXXXXX, if true then contents of L15 (numerical) to be
placed into another worksheet.

I'm not sure what formula to use other than SUMIF to get the contents of
L15; I have SUMIF(J:J,TRUE,OFFSET(L1,5)...I think this is identifying only
the J column criteria as true, I need H column to be true as well. Is this
the right way to move to L column and go down 5 rows to get the amount ? I
would appreciate help writing in the proper syntax. Is this one formula ?
how do I write it ?

 

[Gary''s Student]

Very easy with a "helper" column. Say we use column M; in M1 enter:
=L5 and copy down.

Next switch-on AutoFilter and set the criteria for columns H & J. Only the
"good" rows will be displayed and col M will list all your desired values.

 

[Glenn]

I would like column H to be searched to find xxxx, then when that is found, I
would like column J same row to be searched to find xxxxxxxxxxx, then I would
like to place the amount found in column L five rows below the finding into
another excel spreadsheet.

ie. H10 XXXXX, J10 XXXXXXXX, if true then contents of L15 (numerical) to be
placed into another worksheet.

I'm not sure what formula to use other than SUMIF to get the contents of
L15; I have SUMIF(J:J,TRUE,OFFSET(L1,5)...I think this is identifying only
the J column criteria as true, I need H column to be true as well. Is this
the right way to move to L column and go down 5 rows to get the amount ? I
would appreciate help writing in the proper syntax. Is this one formula ?
how do I write it ?

Look at SUMPRODUCT() at the following:

http://www.contextures.com/xlFunctions01.html

 

[Teethless mama]

=INDEX(L10:L100,MATCH(1,INDEX((H10:H100="xx")*(J10:J100="xxx"),),)+5)

Adjust to suit

 

[Glenn]

Look at SUMPRODUCT() at the following:

http://www.contextures.com/xlFunctions01.html

Sorry, didn't read this correctly.

 

[Elaine]

Thanks so much. I think it works...finally.

=INDEX(L10:L100,MATCH(1,INDEX((H10:H100="xx")*(J10:J100="xxx"),),)+5)

Adjust to suit

 

[Teethless mama]

You're Welcome!

Thanks so much. I think it works...finally.