If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I need to scan a column
I need to scan a column on a second page and look for a name, then scan
another column on the second page and check for how many months it has been (less than)and if the criteria is met to add the totals in another column from the second page and put it in a cell on the first page. So, for example I am on page1 abd I need to look on page2 in A1 - A100 for a name, when it finds the name I need to look on page2 b1-B100 for less than 6 months old and when it is found to add the totals on page2 c1-c100 and out the total into cell a1 on page 1. Hope this make sense. Thank you in advance. |
#2
|
|||
|
|||
I need to scan a column
I'm assuming page1 and page2 mean Sheet1 and Sheet2.
look on page2 b1-B100 for less than 6 months old Assuming that B1:B100 contain numbers like 1, 2, 3, etc =SUMPRODUCT(--(Sheet2!A1:A100="name"),--(Sheet2!B1:B100""),--(Sheet2!B1:B1006),Sheet2!C1:C100) If there will not be any empty cells within the range Sheet2!B1:B100 then you can eliminate one of the arrays: =SUMPRODUCT(--(Sheet2!A1:A100="name"),--(Sheet2!B1:B1006),Sheet2!C1:C100) Empty cells evaluate as 0 and 0 is 6. -- Biff Microsoft Excel MVP "Vercingetorix.XIII" wrote in message ... I need to scan a column on a second page and look for a name, then scan another column on the second page and check for how many months it has been (less than)and if the criteria is met to add the totals in another column from the second page and put it in a cell on the first page. So, for example I am on page1 abd I need to look on page2 in A1 - A100 for a name, when it finds the name I need to look on page2 b1-B100 for less than 6 months old and when it is found to add the totals on page2 c1-c100 and out the total into cell a1 on page 1. Hope this make sense. Thank you in advance. |
#3
|
|||
|
|||
I need to scan a column
Bonjour, Vercingetorix (de nouveau)! I'm a little suspicious about whether
you mean what you seem to have said, so first let me rephrase it and see whether you still agree: If "name1" is in page2!$A$1:$A$100 and "name2" is in page2!$B$1:$B$100 then SUM(page2!$C$!:$C$100) Is that right? Because I gotta wonder whether you don't mean you want to sum ONLY the items in col C that belong to the right name1 and/2 name2. But if the above is what you really want, then it's pretty simple: IF(OR(ISERROR(MATCH("name1",page2!$A$1:$A$100,0)), ISERROR(MATCH("name2",page2!$B$1:$B$100)),0,SUM(pa ge2!$C$!:$C$100)) Breaking that down into pieces: IF( OR( ISERROR( MATCH("name1",page2!$A$1:$A$100,0) ), ISERROR( MATCH("name2",page2!$B$1:$B$100,0) ), 0, SUM(page2!$C$!:$C$100)) MATCH returns either a (relative) line number where the name was found, or #N/A; therefore ISERROR(MATCH...) returns TRUE if the name was not found, FALSE if it was. OR(ISERROR(),ISERROR()) returns TRUE if either MATCH failed, FALSE if both succeeded (ie both names were found). So IF(OR(...),0,SUM(...)) gives you a 0 in this cell if either name was not found, or the sum of the whole col C if both were there. Is that really what you wanted? --- "Vercingetorix.XIII" wrote: I need to scan a column on a second page and look for a name, then scan another column on the second page and check for how many months it has been (less than)and if the criteria is met to add the totals in another column from the second page and put it in a cell on the first page. So, for example I am on page1 abd I need to look on page2 in A1 - A100 for a name, when it finds the name I need to look on page2 b1-B100 for less than 6 months old and when it is found to add the totals on page2 c1-c100 and out the total into cell a1 on page 1. |
#4
|
|||
|
|||
I need to scan a column
Bonjour Bob,
I need to be on sheet one, where I have a list of names and have a formula that goes to sheet 2, looks in A1:100 for the name, checks column B1:100 and looks to see how many months it has been and (if less than 6) it will check column C1:C100 and adds the totals for that name that is less than 6 months old and brings them all over to Column B next to the Name it was searching for. Thanks Bob, as always... "Bob Bridges" wrote: Bonjour, Vercingetorix (de nouveau)! I'm a little suspicious about whether you mean what you seem to have said, so first let me rephrase it and see whether you still agree: If "name1" is in page2!$A$1:$A$100 and "name2" is in page2!$B$1:$B$100 then SUM(page2!$C$!:$C$100) Is that right? Because I gotta wonder whether you don't mean you want to sum ONLY the items in col C that belong to the right name1 and/2 name2. But if the above is what you really want, then it's pretty simple: IF(OR(ISERROR(MATCH("name1",page2!$A$1:$A$100,0)), ISERROR(MATCH("name2",page2!$B$1:$B$100)),0,SUM(pa ge2!$C$!:$C$100)) Breaking that down into pieces: IF( OR( ISERROR( MATCH("name1",page2!$A$1:$A$100,0) ), ISERROR( MATCH("name2",page2!$B$1:$B$100,0) ), 0, SUM(page2!$C$!:$C$100)) MATCH returns either a (relative) line number where the name was found, or #N/A; therefore ISERROR(MATCH...) returns TRUE if the name was not found, FALSE if it was. OR(ISERROR(),ISERROR()) returns TRUE if either MATCH failed, FALSE if both succeeded (ie both names were found). So IF(OR(...),0,SUM(...)) gives you a 0 in this cell if either name was not found, or the sum of the whole col C if both were there. Is that really what you wanted? --- "Vercingetorix.XIII" wrote: I need to scan a column on a second page and look for a name, then scan another column on the second page and check for how many months it has been (less than)and if the criteria is met to add the totals in another column from the second page and put it in a cell on the first page. So, for example I am on page1 abd I need to look on page2 in A1 - A100 for a name, when it finds the name I need to look on page2 b1-B100 for less than 6 months old and when it is found to add the totals on page2 c1-c100 and out the total into cell a1 on page 1. |
#5
|
|||
|
|||
I need to scan a column
Yes T, The first formulas worked beautifully, thank you......
"T. Valko" wrote: I'm assuming page1 and page2 mean Sheet1 and Sheet2. look on page2 b1-B100 for less than 6 months old Assuming that B1:B100 contain numbers like 1, 2, 3, etc =SUMPRODUCT(--(Sheet2!A1:A100="name"),--(Sheet2!B1:B100""),--(Sheet2!B1:B1006),Sheet2!C1:C100) If there will not be any empty cells within the range Sheet2!B1:B100 then you can eliminate one of the arrays: =SUMPRODUCT(--(Sheet2!A1:A100="name"),--(Sheet2!B1:B1006),Sheet2!C1:C100) Empty cells evaluate as 0 and 0 is 6. -- Biff Microsoft Excel MVP "Vercingetorix.XIII" wrote in message ... I need to scan a column on a second page and look for a name, then scan another column on the second page and check for how many months it has been (less than)and if the criteria is met to add the totals in another column from the second page and put it in a cell on the first page. So, for example I am on page1 abd I need to look on page2 in A1 - A100 for a name, when it finds the name I need to look on page2 b1-B100 for less than 6 months old and when it is found to add the totals on page2 c1-c100 and out the total into cell a1 on page 1. Hope this make sense. Thank you in advance. |
#6
|
|||
|
|||
I need to scan a column
Bob, email me, I have a ? for you. Ill try using the email here but usually it wont work. Thanks "Bob Bridges" wrote: Bonjour, Vercingetorix (de nouveau)! I'm a little suspicious about whether you mean what you seem to have said, so first let me rephrase it and see whether you still agree: If "name1" is in page2!$A$1:$A$100 and "name2" is in page2!$B$1:$B$100 then SUM(page2!$C$!:$C$100) Is that right? Because I gotta wonder whether you don't mean you want to sum ONLY the items in col C that belong to the right name1 and/2 name2. But if the above is what you really want, then it's pretty simple: IF(OR(ISERROR(MATCH("name1",page2!$A$1:$A$100,0)), ISERROR(MATCH("name2",page2!$B$1:$B$100)),0,SUM(pa ge2!$C$!:$C$100)) Breaking that down into pieces: IF( OR( ISERROR( MATCH("name1",page2!$A$1:$A$100,0) ), ISERROR( MATCH("name2",page2!$B$1:$B$100,0) ), 0, SUM(page2!$C$!:$C$100)) MATCH returns either a (relative) line number where the name was found, or #N/A; therefore ISERROR(MATCH...) returns TRUE if the name was not found, FALSE if it was. OR(ISERROR(),ISERROR()) returns TRUE if either MATCH failed, FALSE if both succeeded (ie both names were found). So IF(OR(...),0,SUM(...)) gives you a 0 in this cell if either name was not found, or the sum of the whole col C if both were there. Is that really what you wanted? --- "Vercingetorix.XIII" wrote: I need to scan a column on a second page and look for a name, then scan another column on the second page and check for how many months it has been (less than)and if the criteria is met to add the totals in another column from the second page and put it in a cell on the first page. So, for example I am on page1 abd I need to look on page2 in A1 - A100 for a name, when it finds the name I need to look on page2 b1-B100 for less than 6 months old and when it is found to add the totals on page2 c1-c100 and out the total into cell a1 on page 1. |
#7
|
|||
|
|||
I need to scan a column
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Vercingetorix.XIII" wrote in message ... Yes T, The first formulas worked beautifully, thank you...... "T. Valko" wrote: I'm assuming page1 and page2 mean Sheet1 and Sheet2. look on page2 b1-B100 for less than 6 months old Assuming that B1:B100 contain numbers like 1, 2, 3, etc =SUMPRODUCT(--(Sheet2!A1:A100="name"),--(Sheet2!B1:B100""),--(Sheet2!B1:B1006),Sheet2!C1:C100) If there will not be any empty cells within the range Sheet2!B1:B100 then you can eliminate one of the arrays: =SUMPRODUCT(--(Sheet2!A1:A100="name"),--(Sheet2!B1:B1006),Sheet2!C1:C100) Empty cells evaluate as 0 and 0 is 6. -- Biff Microsoft Excel MVP "Vercingetorix.XIII" wrote in message ... I need to scan a column on a second page and look for a name, then scan another column on the second page and check for how many months it has been (less than)and if the criteria is met to add the totals in another column from the second page and put it in a cell on the first page. So, for example I am on page1 abd I need to look on page2 in A1 - A100 for a name, when it finds the name I need to look on page2 b1-B100 for less than 6 months old and when it is found to add the totals on page2 c1-c100 and out the total into cell a1 on page 1. Hope this make sense. Thank you in advance. |
Thread Tools | |
Display Modes | |
|
|