+ Reply to Thread
Results 1 to 4 of 4

Using SUMIF where I need to add up stats for cells that contain a particular text string

  1. #1
    Registered User
    Join Date
    10-01-2008
    Location
    Cheshire
    Posts
    15

    Using SUMIF where I need to add up stats for cells that contain a particular text string

    Hi all,

    I've tried to be as descriptive as possible in the thread title. I've had a look through old posts but haven't seen this particularly query come up although I may not even be searching for the right thing.

    I work in ecommerce and want to produce a report in a table. I have a list of 100 URLs for particular pages of interest on a website in a worksheet and then on another tab I have a list of all pages on said website. I need to use a formula to add up things like sessions, unique users etc for the list of 100 URLs of interest. To complicate matters slightly, the list of all pages contains these URLs but they can be slightly different in format (additional variables at the end of the URL).

    As an example:

    I am interested in a URL abc.com/data

    In the bulk export there are many instances of abc.com/data but some of them will look like "abc.com/data/longerURL" or "abc.com/data/additionaltextstring". If I was only doing it for 1 URL it's easy enough just to set a filter and total off the values that I need. However, as I need to do this for 100 URLs and the reference list is nearly 6,000 rows I'm hoping this can be done with a formula to speed things up.

    Hopefully this gives enough information but please let me know if I can provide further clarification.

    Thanks.

  2. #2
    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,997

    Re: Using SUMIF where I need to add up stats for cells that contain a particular text stri

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on "Reply" then GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    10-01-2008
    Location
    Cheshire
    Posts
    15

    Re: Using SUMIF where I need to add up stats for cells that contain a particular text stri

    Hi & thanks for replying.

    I've attached a very simplified version of how the data looks. I have populated the correct values that the formula would need to return. There is a "data" tab which contains the raw info I'm interested in. The other tab is just a simple report that totals the "sessions" for any instance where the URL in the Data tab contains the exact text string. As you'll see, an example is "/demo/1/thisone
    ".

    On the data tab there are a few different variants such as "/demo/1/thisone/1234" and "/demo/1/thisone/5678" that should both be counted as part of the formula as opposed to entries like "/demo/1/notthisone" which should be omitted from the calculation.

    Hope this helps.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Using SUMIF where I need to add up stats for cells that contain a particular text stri

    in cell c4 =SUMIF(Data!A:A,'Report template'!B4&"*",Data!B:B)

    Regards

    Dav

+ 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. Replies: 4
    Last Post: 09-23-2016, 05:41 PM
  2. Graph to show individual Stats as compared to the population stats
    By Kellbells in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-12-2015, 10:36 AM
  3. Conditional format: mark cells with 1 text string & exclude cells with another string
    By KArnoldColumbia in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-15-2013, 01:43 AM
  4. [SOLVED] SUMIF Query: How to summarise values based on data extracted from a text string
    By The_Snook in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-17-2013, 10:12 AM
  5. [SOLVED] IF range of cells contains string of text, return contents of cell where string is found
    By nobodyukno in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-03-2013, 09:56 AM
  6. [SOLVED] Text string in SUMIF
    By Bravo33 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 02-10-2013, 01:32 PM
  7. SUMIF with criteria text (string)
    By jcprata in forum Excel General
    Replies: 2
    Last Post: 11-20-2009, 11:48 AM

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