Find sum relating to a cell

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

please see big yellow banner and upload your workbook - you are then more likely to get a solution - otherwise you will most likely get numerous informed guesses.

Save one that is small, please - we only need a few rows of data that show the problem.

Have you been deleting or inserting rows at all? Or resorting the data? Formulae do not spontaneously change themselves!

OK. So answers, then, please:

1. Have you at any point reordered the rows oin the table (using sort in the table)?
2. Why have you set the filter on the top row instead of the header row?
3. Have you inserted or deleted any rows?

I'm afraid I don't know, then. There's no test that I can do to check the data's behaviour.

Is it a range in your real data or a structured table?

This might help speed things up a bit:
1. Populate column C using: =FORMULATEXT(B2)
2. Populate column C using: =VALUE(MID(C2,15,SEARCH(",",C2)-SEARCH("/",SUBSTITUTE(C2,"A","/",2))-1))
3. Apply conditional formatting to column D using: =D2<>SUM(D1,1)
This highlights three cells in the sample, however it reduces the number of cells needing inspection by 70%.
That said, an easy correction, once any discrepancies are seen, would be to double click the fill handle of cell B2.
Let us know if you have any questions.

Originally Posted by rikknikk

the real data set is in a structured table

OK - please try using a structured formula. Remove ALL of the formulae in the column in question and add a structured formula like this in the equivalent of B2:

=COUNTIF(A$2:[@[Store ID]],[@[Store ID]])

It will spill down the table.

I would recommend using the normal formula with anchor on header row: =COUNTIF(A$1:A2,A2).

This formula should stay even if you should delete the first row or any other rows and will also not face any issue with sorting.

With Excel structured table, the formula will also auto-expand to include any new items added at the bottom of the table or in-between existing rows.

In a structured table, that would need to be this:

=COUNTIF(A$1:[@[Store ID]],[@[Store ID]])

No - it doesn't error at all. I can delete the top row altogether and I can even just delete top row contents - the formula still works.

All tested in the attached.

Did you think I would just repeat the same thing again?

Please provide a sample workbook for us to look at.

What is the new formula meant to be doing?

Try this:

=IF(COUNTIF([Store ID],[@[Store ID]])=COUNTIF(A$1:[@[Store ID]],[@[Store ID]]),"Latest","")

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Thanks for the rep.