Hello everyone,

I'm new to USING formulas in excel to shortcut some of the more tedious workbook calculations. I've been getting on relatively well since I've began but I've come across a problem I can't seem to solve and would appreciate any help you can offer. I am using Excel 2010.

The goal:

I am trying so sum up all the values in column "J" that match multiple criteria’s in column "B", and only one criteria in columns "E" and "K."

As you will see in the formula below, I would like to include values in column "B" that include a certain strings of text (e.g. "*SAO A*", "*PCP F*", etc.), while excluding other similar strings of text (only differentiated by a "+ " which proceeds the string; e.g. "*+ SAO A*", etc.).

- I am looking for text strings in column "B", "E" and "K" (for column "K", I am only looking for " " e.i. a blank cell which only has a "space" in it, as in space bar on the keyboard). Column "J" contains numbers.

- The "*" are there because the string I am looking for will often be found among a longer strings.

- The amounts of rows in the worksheet can vary (they are not fixed).

The problem:

The formula I used (below) works but the resulting sum is higher than it should be and I suspect it is because there is double counting going on. I think there is double counting because it is often the case that "SAO A", "SAO F", "PCP A", and "PCP F" often find themselves in the same cell (usually SAO A and PCP A but it can also be another combination of these 4 strings, including those with "+ ").

Does this double counting make sense? Could it explain the inflated sums I find?

If so, can you please help me figure out how could I get around this problem?

The formula I'm using:

=SUM(SUMIFS(J:J,B:B,{"*SAO A*","*SAO F*","*PCP A*","*PCP F*"},E:E, M2,K:K, " ")) - SUM(SUMIFS(J:J,B:B,{"*+ SAO A*","*+ SAO F*","*+ PCP A*","*+ PCP F*"},E:E, M2,K:K, " "))

From what I've read online, it seems like SUMPRODUCT would be better for this task but I can't get it to work at all (either sums to "0" or error). Furthermore, because the row amount in my worksheet is variable, I have read that it might be too resource intensive to use SUMPRODUCT for entire columns.

Thank you for your help!

## Bookmarks