+ Reply to Thread
Results 1 to 10 of 10

Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 criteria

  1. #1
    Registered User
    Join Date
    05-11-2016
    Location
    london
    MS-Off Ver
    2011
    Posts
    7

    Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 criteria

    Hi,

    I have created a SUMPRODUCT/ SUMIF formula with multiple criteria to return the number of impressions that correspond with adverts by a specific competitor which contain any of a range of text.

    =SUMPRODUCT(SUMIFS(Raw!F:F, Raw!A:A,"*"&A2&"*",Raw!$B:$B,"*"&Themes!$A$2:$A$28&"*"))/SUMIF( Raw!A:A,"*"&A2&"*", Raw!F:F)

    HOWEVER, this formula is counting impressions twice if two of the range of text are within 1 ad text cell. I need the formula to count the impression associated with the ad once if any of the range of texts are contained in it.

    Example:

    Advertiser: Adidas
    Ad: Buy online today
    Impressions: 3000

    Range of 'Themes': Buy, Today

    Returns: 6000 when I would need it to return 3000.

    I have tried translating to a ISNUMBER(SEARCH formula but failed so far - It must not be case sensitive either.

    Any help appreciated!

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

    Hi,

    You can use:
    =SUMPRODUCT(RAW!F:F,(ISNUMBER(SEARCH(A2,RAW!A:A)))*(COUNT(SEARCH(Themes!$A$2:$A$28,RAW!$B:$B))>0))/SUMIF( RAW!A:A,"*"&A2&"*", RAW!F:F)

    but I recommend you avoid referring to entire columns in such a formula, especially so if you repeat the formula a lot.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    05-11-2016
    Location
    london
    MS-Off Ver
    2011
    Posts
    7

    Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

    Hi,

    Thanks for the reply but this is only returning 0 now which is incorrect.

    I have attached a sample data to show this.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

    You need to enter the formulae with Ctrl+Shift+Enter

    plus your formula in C20 refers to B20 not A20

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

    Quote Originally Posted by JohnTopley View Post
    You need to enter the formulae with Ctrl+Shift+Enter
    Thank you. Yes, I should have made that clear. Additionally, there is no need for SUMPRODUCT when SUM will achieve the same result.

  6. #6
    Registered User
    Join Date
    05-11-2016
    Location
    london
    MS-Off Ver
    2011
    Posts
    7

    Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

    Thanks a lot for your help! It is working with SUMPRODUCT now but SUM does give an inflated number.

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

    Apologies, I was thinking of something else. You do indeed require SUMPRODUCT for this.

  8. #8
    Registered User
    Join Date
    05-11-2016
    Location
    london
    MS-Off Ver
    2011
    Posts
    7

    Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

    Hi,

    Apologies for opening this again but I am struggling to apply this formula to my original data. the formula is just counting all impressions where the first SEARCH criteria is true but ignoring the second.

    I have attached a small set, if anyone could help me understand why this is not working please?

    Thanks.
    Attached Files Attached Files

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

    It is I who must apologise I'm afraid. The formula I suggested does not work for this at all. I will do my best to rectify the situation.

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Need SUMPRODUCT/ SUMIF formula to sum each cell once not dupe if text contains 2 crite

    Perhaps you might be able to use a DSUM solution? I have attached an example workbook.
    Attached Files Attached Files

+ 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. SUMIF or SUMPRODUCT formula to SUM totals based on multiple criteria
    By relmasri in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 06-01-2016, 01:08 PM
  2. [SOLVED] Need insight adjusting SUMPRODUCT or SUMIF formula to capture multiple criteria
    By jnswbc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2016, 11:57 AM
  3. [SOLVED] Sumif Formula or Sumproduct Formula? Three Criteria.
    By oHUTCHYo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2013, 10:50 AM
  4. SumIF formula with criteria is Text inside a Formula?
    By excellentlearner in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-25-2012, 06:40 PM
  5. Formula to dupe cell contents from one file to another?
    By Stratvan in forum Excel General
    Replies: 1
    Last Post: 06-25-2009, 03:16 AM
  6. SUMIF based on text criteria from another cell
    By craigproudfoot in forum Excel General
    Replies: 2
    Last Post: 03-16-2009, 08:33 AM
  7. [SOLVED] SUMIF/SUMPRODUCT/IF - 2 criteria - Date and Text
    By James T in forum Excel General
    Replies: 4
    Last Post: 05-25-2006, 03:00 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