Sum on multiple columns with search criteria

  • Thread starter Thread starter steve711
  • Start date Start date
S

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
 
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)
 
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)
 
Brilliant.

Many thanks for your help.

Steve



T. Valko said:
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)
Click to expand...
 
Brilliant.

Many thanks for your help.

Steve



T. Valko said:
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)
Click to expand...
 
Back
Top