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.

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.

Please attach a worksheet, NOT a picture of one.

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

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.

In "C2" criteria like Woolworth*
In "D2"

Formula:

Please Login or Register  to view this content.

sumif with wildcard