Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum on multiple columns with search criteria
I am trying to sum a column if the criteria matches for two other cloumns
across different spreadsheets or tabs. However, one of the colums has a text search. For example: Spreadsheet 1 Column A ColumnB Column C (sumif) 900-1203 Wilson ++{SUM(IF(('GL April 2009'!$E$2:$E$3000="*"&$A21&"*")*('GL April 2009'!$G$2:$G$3000="*"&$E21&"*"),('GL April 2009'!$F$2:$F$3000),0))} GL April 2009 column E(acct) Column G (lookup list) Column F(amount) 900-1203 WE 11/15/08-Wilson $1,000 I have many names and accounts. Say I have 15 names and accounts. I am tyring to sumif based the multiple criteria of 900-1203 AND Wilson. I am referencing the cell (spreasheet 1, column A and B) for a search on spreasheet 2 and using a string "*"&B1&"*") not tying in the name (i.e. "*wilson*"). I can get the formuals to work individually but not in combination. I have tried many different things including, sumif, sum(if) and sumproduct and reorganizing by pivot tables and vlookups but nothing really works. Can anybody help me? Thanks, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum on multiple columns with search criteria
Try one of these...I'm leaving out the sheet name so be sure to add it.
A21 = 900-1203 B21 = Wilson =SUMPRODUCT(--($E$2:$E$3000=A21),--(ISNUMBER(SEARCH(B21,$G$2:$G$3000))),$F$2:$F$3000) If the search criteria (in this case, Wilson) is *always* at the very end of the string: WE 11/15/08-Wilson =SUMPRODUCT(--($E$2:$E$3000=A21),--(RIGHT($G$2:$G$3000,LEN(B21))=B21),$F$2:$F$3000) -- Biff Microsoft Excel MVP "steve711" wrote in message ... I am trying to sum a column if the criteria matches for two other cloumns across different spreadsheets or tabs. However, one of the colums has a text search. For example: Spreadsheet 1 Column A ColumnB Column C (sumif) 900-1203 Wilson ++{SUM(IF(('GL April 2009'!$E$2:$E$3000="*"&$A21&"*")*('GL April 2009'!$G$2:$G$3000="*"&$E21&"*"),('GL April 2009'!$F$2:$F$3000),0))} GL April 2009 column E(acct) Column G (lookup list) Column F(amount) 900-1203 WE 11/15/08-Wilson $1,000 I have many names and accounts. Say I have 15 names and accounts. I am tyring to sumif based the multiple criteria of 900-1203 AND Wilson. I am referencing the cell (spreasheet 1, column A and B) for a search on spreasheet 2 and using a string "*"&B1&"*") not tying in the name (i.e. "*wilson*"). I can get the formuals to work individually but not in combination. I have tried many different things including, sumif, sum(if) and sumproduct and reorganizing by pivot tables and vlookups but nothing really works. Can anybody help me? Thanks, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum on multiple columns with search criteria
Brilliant.
Many thanks for your help. Steve "T. Valko" wrote: Try one of these...I'm leaving out the sheet name so be sure to add it. A21 = 900-1203 B21 = Wilson =SUMPRODUCT(--($E$2:$E$3000=A21),--(ISNUMBER(SEARCH(B21,$G$2:$G$3000))),$F$2:$F$3000) If the search criteria (in this case, Wilson) is *always* at the very end of the string: WE 11/15/08-Wilson =SUMPRODUCT(--($E$2:$E$3000=A21),--(RIGHT($G$2:$G$3000,LEN(B21))=B21),$F$2:$F$3000) -- Biff Microsoft Excel MVP "steve711" wrote in message ... I am trying to sum a column if the criteria matches for two other cloumns across different spreadsheets or tabs. However, one of the colums has a text search. For example: Spreadsheet 1 Column A ColumnB Column C (sumif) 900-1203 Wilson ++{SUM(IF(('GL April 2009'!$E$2:$E$3000="*"&$A21&"*")*('GL April 2009'!$G$2:$G$3000="*"&$E21&"*"),('GL April 2009'!$F$2:$F$3000),0))} GL April 2009 column E(acct) Column G (lookup list) Column F(amount) 900-1203 WE 11/15/08-Wilson $1,000 I have many names and accounts. Say I have 15 names and accounts. I am tyring to sumif based the multiple criteria of 900-1203 AND Wilson. I am referencing the cell (spreasheet 1, column A and B) for a search on spreasheet 2 and using a string "*"&B1&"*") not tying in the name (i.e. "*wilson*"). I can get the formuals to work individually but not in combination. I have tried many different things including, sumif, sum(if) and sumproduct and reorganizing by pivot tables and vlookups but nothing really works. Can anybody help me? Thanks, Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum on multiple columns with search criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "steve711" wrote in message ... Brilliant. Many thanks for your help. Steve "T. Valko" wrote: Try one of these...I'm leaving out the sheet name so be sure to add it. A21 = 900-1203 B21 = Wilson =SUMPRODUCT(--($E$2:$E$3000=A21),--(ISNUMBER(SEARCH(B21,$G$2:$G$3000))),$F$2:$F$3000) If the search criteria (in this case, Wilson) is *always* at the very end of the string: WE 11/15/08-Wilson =SUMPRODUCT(--($E$2:$E$3000=A21),--(RIGHT($G$2:$G$3000,LEN(B21))=B21),$F$2:$F$3000) -- Biff Microsoft Excel MVP "steve711" wrote in message ... I am trying to sum a column if the criteria matches for two other cloumns across different spreadsheets or tabs. However, one of the colums has a text search. For example: Spreadsheet 1 Column A ColumnB Column C (sumif) 900-1203 Wilson ++{SUM(IF(('GL April 2009'!$E$2:$E$3000="*"&$A21&"*")*('GL April 2009'!$G$2:$G$3000="*"&$E21&"*"),('GL April 2009'!$F$2:$F$3000),0))} GL April 2009 column E(acct) Column G (lookup list) Column F(amount) 900-1203 WE 11/15/08-Wilson $1,000 I have many names and accounts. Say I have 15 names and accounts. I am tyring to sumif based the multiple criteria of 900-1203 AND Wilson. I am referencing the cell (spreasheet 1, column A and B) for a search on spreasheet 2 and using a string "*"&B1&"*") not tying in the name (i.e. "*wilson*"). I can get the formuals to work individually but not in combination. I have tried many different things including, sumif, sum(if) and sumproduct and reorganizing by pivot tables and vlookups but nothing really works. Can anybody help me? Thanks, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple search criteria | Excel Worksheet Functions | |||
Search for multiple criteria, and return yet another | Excel Worksheet Functions | |||
Multiple search criteria to return one response | Excel Worksheet Functions | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Search string with multiple criteria | Excel Worksheet Functions |