Sum on multiple columns with search criteria

[steve711]

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

 

[T. Valko]

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)

 

[steve711]

Brilliant.

Many thanks for your help.

Steve

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)

 

[T. Valko]

You're welcome. Thanks for the feedback!