+ Reply to Thread
Results 1 to 12 of 12

Problem with a sum calculation

  1. #1
    Registered User
    Join Date
    02-12-2019
    Location
    Manchester, UK
    MS-Off Ver
    Excel for Mac - Windows 365
    Posts
    3

    Question Problem with a sum calculation

    Hi all

    I wonder if you can help me. I have various merchants in column A and values spent in column b but as you can see from the example below some merchants differentiate the actual store. Can you please advise how I would be able to get the sum total for all McDonalds and all the Coles in the example below? These are just examples the actual table has in excess of 20,000 rows.

    MCDONALDS BRIS TRANQPS 14
    MCDONALDS QUEENSTOWN 12
    COLES 0671 36
    COLES 3245 76
    COLES 6578 32

    Thank you

    Rob

  2. #2
    Forum Contributor
    Join Date
    11-21-2017
    Location
    Chorley
    MS-Off Ver
    2013
    Posts
    173

    Re: Problem with a sum calculation

    Hi and Welcome to the Forum

    If you can reply on there always being a space after the first Merchant then in C you could use;

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If not then post a more representative data set in a Workbook and we'll see what else we can come up with

    EDIT: If you just wanted to do it manually in a summary table then you could also use something like;

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or just use a cell reference that has "MCDONALDS" in it;

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by PaulSP8; 02-12-2019 at 07:02 AM. Reason: More options

  3. #3
    Banned User!
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    188

    Re: Problem with a sum calculation

    hi,
    please see the image as i understand.
    Attached Images Attached Images

  4. #4
    Registered User
    Join Date
    01-30-2019
    Location
    Hyderabad
    MS-Off Ver
    2007
    Posts
    1

    Re: Problem with a sum calculation

    =sumproduct(sumifs(b7:b11,a7:a11,{"*mcdonalds*"}))

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,044

    Re: Problem with a sum calculation

    majidsiddique - please don't post images. You should post the formula so that it can be copied and pasted, and you could also attach the workbook. Images cannot be manipulated.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  6. #6
    Registered User
    Join Date
    02-12-2019
    Location
    Manchester, UK
    MS-Off Ver
    Excel for Mac - Windows 365
    Posts
    3

    Re: Problem with a sum calculation

    Hi all

    Thank you so much for your replies but I think I may have confused things with the table I included so I have updated below. I would like in this example to the the sum total for all Woolworths, all Tesco and all McDonalds. Thanks.
    Attached Images Attached Images

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,044

    Re: Problem with a sum calculation

    Please attach a worksheet, NOT a picture of one.

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,912

    Re: Problem with a sum calculation

    Hi darbyrw,

    Find the attached where I've added a helper column to combine your Merchant and Store names so I could do pivot tables. See if this gives you what you wanted.

    Pivot Tables after Helper.xlsx
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  9. #9
    Registered User
    Join Date
    02-12-2019
    Location
    Manchester, UK
    MS-Off Ver
    Excel for Mac - Windows 365
    Posts
    3

    Re: Problem with a sum calculation

    Hi - Apologies this is the first time I have used the forum and I tried to add a worksheet but the columns kept going out of sync - is the a guide on uploading to the forum? Thanks

  10. #10
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    367

    Re: Problem with a sum calculation

    I'm not sure what you mean by
    I tried to add a worksheet but the columns kept going out of sync
    .
    Assuming that you are trying to attach a workbook, not a worksheet then the following should help you:

    To attach a file, first make sure to remove any sensitive/proprietary data and then click on “GO ADVANCED” and then scroll down to “Manage Attachments” to open the upload window. Choose your file then click on “Upload”, scroll down then click on “Close this window”, then “Submit reply”.

  11. #11
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010
    Posts
    367

    Re: Problem with a sum calculation

    Isn't the problem here that Rob has no foolproof way to separate the merchant from the stores? Per post #1 he has 20,000 rows so it will be more than tedious to do it manually.
    The following in C2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    And in D2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    gets close, sort of, with the little amount of data provided so far (but look at Tesco):

    merchants.png

    To refine this formula will need a longer list of merchant/store name examples and maybe there will still be exceptions that cannot reasonably be caught with a single formula. In that case a lookup table between as-provided store/merchant name and separate merchant and store names may be needed.

    Once this part of the problem is solved then, per MarvinP's post #8, a pivot table can be used to get the required per-merchant summaries.
    Last edited by GeoffW283; 02-16-2019 at 12:31 AM.

  12. #12
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,262

    Re: Problem with a sum calculation

    In "C2" criteria like Woolworth*
    In "D2"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    sumif with wildcard


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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