SUMIFS - double counting?

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!

You get better help on your question if you follow the advice in the link below.

http://www.excelforum.com/the-water-...-question.html

Thank you oeldere. I've attached a sample file!

Geoff.

Geoff., Good afternoon.

"...Does this double counting make sense? Could it explain the inflated sums I find? ..."
Yes.
At first part of formula it sums 144,48 and second part 28,1
Then 144,48 - 28,1 = 116,38

The expected result value would be 71,82?

With an helpcolumn (I used VLookup to determine the values).

After that I made an pivot table with the sum and average.

See the attached file.

Then you need to tell me what the criteria are (to get to the result).

You could add manualy the result in your sheet and use the same color for the cells that are related.

Why does cell B5 not match the criteria.

Please explain all criteria.