So I've trying to create a formula that will mimic a sumif function but using multiple criteria. One of the criteria is to look for a certain character string that could be inside of a cell that contains other characters as well. I know if I do a sumif function I can use a formula like this:
=SUMIF(Sheet1!$B:$B,"*" & "Test" & "*",Sheet1!$F:$F)
That would look in row B for any cells that had the string Test anywhere inside the cell and then sum up column F. My problem is that I need a formula that will look for a certain string as well as make sure it matches another value. I've tried using this formula:
=SUMPRODUCT(--(Sheet1!B1:B100="*" & "Test" & "*"),--(Sheet1!C1:C100=20),Sheet1!F1:F100)
That is looking in column B for a string of Test and then also making sure that column C = 20. Fopr any matches it sums up column F. The formula doesn't work though because it doesn't allow the function I have setup to look for Test. Can anyone help me with a solution. Thanks
Bookmarks