Checking subtotals with varying entries

[bd528]

Hi,

Please see attached.

Entries in column B indicate a new entry. What I need to do is to check that the sum of values in column G equal the corresponding value in column B. So, Entry B is incorrect for example, as 40,20 & 30 does not equal.

In the real example, here are hundreds of enties. Each entry has varying numbers of rows.

Any assistance would be great.

Thanks.

[DonkeyOte]

Assuming values in B are text then the below will work:

Please Login or Register  to view this content.

[bd528]

Amazing, thank you.

You've just made my day at work far less stressful

[bd528]

Hi,

I tried to use the code in the real example, but I'm struggling. Plus there is an additional factor now.

Please see attached.

Now I need the forumla to show :-

"Correct" if the total of column M (per section) plus the value in column D equals column C - otherwise "Wrong" (as per my example)

Many thanks.

[bd528]

Sorry, forgot attachment...

[DonkeyOte]

Your references:

"Correct" if the total of column M (per section) plus the value in column D equals column C - otherwise "Wrong" (as per my example)

..don't tie out to your example file

Do you mean: check value in Ex = Dx where Bx is not blank ?

Might make sense to post some examples where the results are Wrong - whether that be the result of the values not totalling per Group or the other condition is not met.

[bd528]

Sorry about that, I attached the wrong file.

There are some examples of wrong and right.

Thanks

[DonkeyOte]

OK so

Please Login or Register  to view this content.

[bd528]

Thats works great.

Thanks again for your help. Very clever stuff.

How can I flag this thread as solved?

[DonkeyOte]

No problem

See FAQ / How To ? for info. on marking threads Solved.

[bd528]

Maybe you might be able to help with 2 more possible ammendments? Sorry I should have thought the query through before my original post...

(This may over-complicate the formula)

Please see attached

In column E, where the value for the section is IRISTD, I'd like the formula to ignore the value in column D. So the comparison should just be against the total of column M and the value in column C.

Secondly, I recorded a macro to insert a column, paste the formula, and then copy it down. Is there a way to stop the formula being copied once it reaches the last row? (row 12 in my example)

The code I'm using at the moment is

Please Login or Register  to view this content.

Thanks again

[DonkeyOte]

No attachment...

[bd528]

I'm sure I attached it that time!

Here you go...

[DonkeyOte]

Based on my understanding:

Please Login or Register  to view this content.

[bd528]

Yep, that worked perfectly again. And the macro generated the results even faster.

Thanks again for your time and effort

Solved!