# Problem with a sum calculation

1. ## 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. ## 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:
`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:
`Please Login or Register  to view this content.`

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

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Problem with a sum calculation

hi,
please see the image as i understand.

4. ## Re: Problem with a sum calculation

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

5. ## 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.

6. ## 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.

7. ## Re: Problem with a sum calculation

Please attach a worksheet, NOT a picture of one.

8. ## 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

9. ## 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. ## 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. ## 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:
`Please Login or Register  to view this content.`
And in D2:
Formula:
`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.

12. ## Re: Problem with a sum calculation

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

sumif with wildcard

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

#### 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