+ Reply to Thread
Results 1 to 10 of 10

SUMIFS - double counting?

  1. #1
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    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!
    Attached Files Attached Files
    Last edited by Geoff.; 05-01-2014 at 01:34 PM. Reason: Attached sample excel worksheet

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: SUMIFS - double counting?

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

    http://www.excelforum.com/the-water-...-question.html
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: SUMIFS - double counting?

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

    Geoff.
    Last edited by Geoff.; 05-01-2014 at 01:35 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    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?
    ...If my answer helped you, Please, click on. * Add Reputation (at left)

    Best regards.
    Marcílio Lobão

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    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.

  6. #6
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: SUMIFS - double counting?

    Quote Originally Posted by oeldere View Post
    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?

    Quote Originally Posted by Mazzaropi View Post
    [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.

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    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.

  8. #8
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    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.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: SUMIFS - double counting?

    Why does cell B5 not match the criteria.

    Please explain all criteria.

  10. #10
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Avoiding Double Counting
    By sh55174 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-07-2014, 09:40 PM
  2. Countifs and Double Counting
    By jlacsina in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-18-2013, 09:55 PM
  3. Sumifs double nested condictional
    By JayReina in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-16-2013, 03:32 PM
  4. Difficulties with counting overtime, double IF's
    By mako in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-23-2007, 09:19 AM
  5. Double Counting in Pivot Tables
    By CYB in forum Excel General
    Replies: 0
    Last Post: 08-10-2005, 07:18 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1