# SUMIFS - double counting?

1. ## 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?

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.   Register To Reply

2. ## Re: SUMIFS - double counting?

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

3. ## Re: SUMIFS - double counting?

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

Geoff.  Register To Reply

4. ## Re: SUMIFS - double counting?

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?  Register To Reply

5. ## Re: SUMIFS - double counting?

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.  Register To Reply

6. ## Re: SUMIFS - double counting? Originally Posted by oeldere 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.
Thank you for preparing that file oeldere. It is not arriving at the answer I am trying to get however. Mazzaropi arrived at the right answer though. I think you are on the right track, but I don't really understand the use of the HELPCOLUMN and VLOOKUP. Is it possible to just use a formula for this? Similar to the formula I have used? Originally Posted by Mazzaropi [B]
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?
Yes the expected result would be 71.82. Is it possible to arrive at the correct result by using a single formula rather than using a pivot table. Ideally the result would be arrived at within a single cell (the one which contains the formula). I suppose the porblem is that SUMIFS does not use the "OR" operation, only the "AND."

Does this make sense?

Thank you,

Geoff.  Register To Reply

7. ## Re: SUMIFS - double counting?

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.  Register To Reply

8. ## Re: SUMIFS - double counting?

Hi oeldere,

I've made the additions you mentioned in the following file. Please let me know if you require further details.

Does this more sense now?

Thanks,

Geoff.  Register To Reply

9. ## Re: SUMIFS - double counting?

Why does cell B5 not match the criteria.  Register To Reply

10. ## Re: SUMIFS - double counting?

Hi oeldere,

B5 does not match the criteria because PCP F is preceded by a "+." Please see the first bullet below.

Thanks

Criteria description:
- Sum content of cell J if cell in column B contains SAO A or SAO F or PCP A or PCP F but not if any of these are preceded by "+" (e.i., + SAO A or + SAO F or + PCP A or + PCP F)
- Sum content of cell J if cell in column E = M2
- Sum content of cell J if cell K = " " (where " " represents a space)  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 