Convert SUMIFS formula from 2007 to 2003

F

Fellipe C. Moreira

Hello, I have one here that I can't convert as well:

=SUMIFS(Sheet1!D2:D11,Sheet1!A2:A11,Sheet2!$A2,Sheet1!C2:C11,"*"&Sheet2!B$1&"*")

Any clue?

Thanks in advance!
 
B

Bob Umlas

=SUMPRODUCT(N(Sheet1!A2:A11=Sheet2!$A2),N(NOT(ISERROR(FIND(Sheet2!B1,Sheet1!C2:C11)))),Sheet1!D2:D11)
 
F

Fellipe C. Moreira

Thanks for the quick response Bob!

However I couldn't make it work, it's giving me 0 when it should give me
other value as the other does, any idea?
 
T

T. Valko

=SUMIFS(Sheet1!D2:D11,Sheet1!A2:A11,Sheet2!$A2,Sheet1!C2:C11,"*"&Sheet2!B$1&"*")

Try this:

=SUMPRODUCT(--(Sheet1!A2:A11=Sheet2!A2),--(ISNUMBER(SEARCH(Sheet2!B1,Sheet1!C2:C11))),Sheet1!D2:D11)
 
F

Fellipe C. Moreira

This one worked, thanks a lot!

T. Valko said:
Try this:

=SUMPRODUCT(--(Sheet1!A2:A11=Sheet2!A2),--(ISNUMBER(SEARCH(Sheet2!B1,Sheet1!C2:C11))),Sheet1!D2:D11)
Click to expand...
 
Top