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

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

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

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