why are my bottom two contras not matching 2? can someone help

[esaelias]

Why the the green lines not saying 2?

the top lines are fine

iv concatanetd the name, amount,
i have a countif in there also

[esaelias]

here i forgot to add the file thanks alot guys

[popipipo]

C7 and C8 are different
C9 and C10 are different

I don't know what you want to count in column E

[esaelias]

sorry i cannot upload a file as its too large but if you can help i would appreciate it:

bascically

in column AD it counts how many 180 there are above - its got over 100,000 lines so if you look at cell AI 971, at the end it says 180-4, this is because it looks at column AD and see how many times before 180 has come so it says its seen 18- 4 times before ( i am trying to net the same figures) problem is row 973 should match 971 but because the concat at the end says 180-4 & 180-1 it counts that as two separate lines and gives me the incorrect number 1 in column AJ

please see picture

if you look at column AI cels 971 and 973 they are exactly the same and should net off, but due to concat and countifs it doesnt match

does it have to be sorted a special way?

Attachment 868607

[esaelias]

C7 and C8 are different
C9 and C10 are different

I don't know what you want to count in column E

sorry please see above my explnatation if you can help would really apprecaite

[esaelias]

iv summarised more clearly:

Hi All,

I thought this formula was working as it did find a lot of contra entries, which then shown in column AJ a 1 or 2 – 2 meant it has a matching contra based on the concat in AI, 1 meant it cannot find a matching entry

I was happy until I randomly checked an amount (180) and realised it as 1 when it should have been 2

In column AD it counts how many 180 there are above (its filtered on 180) - its got over 100,000 lines so if you look at cell AI 971, at the end it says 180-4, this is because it looks at column AD and see how many times before 180 has come - so it says its seen 180- 4 times before ( i am trying to net the same figures) problem is row 973 should match 971 but BECAUSE the concat at the end says 180-4 & 180-1 it counts that as two separate lines (non matching) and gives me the incorrect number 1 in column AJ


if you look at column AI cells 971 and 973 they are exactly the same and should net off, but due to concat and countifs it doesn’t match, it thinks its different

I also I think need the countifs formula in Column AI as it if do not the countif in column AJ will show more numbers than 1 or 2 which is incorrect

As an exercise I deleted the 180’s in cells AD 73,136,220 and guess what the cell AJ 971/972 show a 2

[esaelias]

also posted here:

https://www.mrexcel.com/board/thread...-5gbp.1258644/

[joris moerings]

Without seeing the file or when looking at the one posted on Excel Forum, i'm see 2 potential issues:

You're mentioning wanting to count anything "above the current cell/row" however your formula is referring the total column. Doing it like this will never come back with a valid answer to "counts how many 180 there are above". To get a valid and correct result from the formula, you need to restrict the counting range.
Your count involves 2 criteria, so my advise change to "countifs" and include both criteria. Perhaps change the countif in column X to

Please Login or Register  to view this content.

Any further help: please attach the appropriate file to this thread, the one uploaded in post 4 is invalid.