EXCEL FORMULA , MEET 2 CRITERIA THEN SUM
 

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 ?

EXCEL FORMULA , MEET 2 CRITERIA THEN SUM
 

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.
--
Gary''s Student - gsnu200816


"Elaine" wrote:

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 ?

EXCEL FORMULA , MEET 2 CRITERIA THEN SUM
 

Elaine wrote:
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

EXCEL FORMULA , MEET 2 CRITERIA THEN SUM
 

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

Adjust to suit


"Elaine" wrote:

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 ?

EXCEL FORMULA , MEET 2 CRITERIA THEN SUM
 

Glenn wrote:
Elaine wrote:
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

Sorry, didn't read this correctly.

EXCEL FORMULA , MEET 2 CRITERIA THEN SUM
 

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

"Teethless mama" wrote:

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

Adjust to suit


"Elaine" wrote:

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 ?

EXCEL FORMULA , MEET 2 CRITERIA THEN SUM
 

You're Welcome!


"Elaine" wrote:

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

"Teethless mama" wrote:

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

Adjust to suit


"Elaine" wrote:

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 ?