| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to get the median of cells that meet a given criteria in excel | Excel Worksheet Functions | |||
| How to get Excel to Recalculate a formula if the answer does not meet criteria | Excel Worksheet Functions | |||
| Formula/Macro to delete rows that do not meet criteria from a list? | Excel Worksheet Functions | |||
| Formula that only adds numbers that meet specific criteria | Excel Discussion (Misc queries) | |||
| how do an @if formula to meet criteria where x is divisible by 12 | Excel Worksheet Functions | |||
Hybrid Mode
