Countif contra formula

[kingjet]

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


btw the concat is the invoice number, client name, and the amount - to make it very unique

 

[kingjet]

also posted here: why are my bottom two contras not matching 2? can someone help

 

[jorismoerings]

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

1. 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.
2. Your count involves 2 criteria, so my advise change to "countifs" and include both criteria.

Any further help: please use the XL2BB add in to help you post a usefull snippet of your sheet.

 

[kingjet]

thanks alot ill do iit now

 

[jorismoerings]

Perhaps something like this:

NEw check.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Invoice Separated	Customer Name	Sum of Revenue	ABS	Countif	Concat	Countif	Expected		New
2	1390951	Sahara	-2985	2985	1	29851390951Sahara-1	2			2
3	1390951	Sahara	2985	2985	1	29851390951Sahara-1	2			2
4	1393230	Sahara	-2985	2985	2	29851393230Sahara-2	2			2
5	1393230	Sahara	2985	2985	2	29851393230Sahara-2	2			2
6	1394846	Virgin	2985	2985	3	29851394846Virgin-3	1			1
7	12569	Plastic	-2985	2985	3	298512569Plastic-3	1	shoul be 2		2
8	12569	Plastic	2985	2985	4	298512569Plastic-4	1	shoul be 2		2
9	1397943	Plastic	-2985	2985	4	29851397943Plastic-4	1	should be 2		2
10	1390957	ALi	2077,69	2077,69	1	2077,691390957ALi-1	2			2
11	1390957	ALi	-2077,69	2077,69	1	2077,691390957ALi-1	2			2
12	1390961	Univar Speciality Consumables Limited	1696,6	1696,6	1	1696,61390961Univar Speciality Consumables Limited-1	1			1
13	1390962	Felicia	565,64	565,64	1	565,641390962Felicia-1	1			1
14	1390963	Tele	678,19	678,19	1	678,191390963Tele -1	1			1
15	1390963	Tele	3049,36	3049,36	1	3049,361390963Tele -1	1			1
16	1390964	Remington	884,34	884,34	1	884,341390964Remington-1	1			1
17	123456	Plastic Reinforcement Fabrics Limited	2985	2985	5	2985123456Plastic Reinforcement Fabrics Limited-5	2			2
18	123456	Plastic Reinforcement Fabrics Limited	-2985	2985	5	2985123456Plastic Reinforcement Fabrics Limited-5	2			2
39	1397943	Plastic	2985	2985	6	29851397943Plastic-6	1	should be 2		2
Sheet1

Cell Formulas
Range	Formula
D2:D18,D39	D2	=ABS(C2)
E2:E18,E39	E2	=COUNTIF($C$2:C2,C2)
F2:F18,F39	F2	=CONCATENATE(D2,A2,B2,"-",E2)
G2:G18,G39	G2	=COUNTIF($F$2:$F$33515,F2)
J2:J18,J39	J2	=COUNTIFS($D$2:$D$39,D2,$A$2:$A$39,A2,$B$2:$B$39,B2)

 

[kingjet]

Perhaps something like this:

NEw check.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Invoice Separated	Customer Name	Sum of Revenue	ABS	Countif	Concat	Countif	Expected		New
2	1390951	Sahara	-2985	2985	1	29851390951Sahara-1	2			2
3	1390951	Sahara	2985	2985	1	29851390951Sahara-1	2			2
4	1393230	Sahara	-2985	2985	2	29851393230Sahara-2	2			2
5	1393230	Sahara	2985	2985	2	29851393230Sahara-2	2			2
6	1394846	Virgin	2985	2985	3	29851394846Virgin-3	1			1
7	12569	Plastic	-2985	2985	3	298512569Plastic-3	1	shoul be 2		2
8	12569	Plastic	2985	2985	4	298512569Plastic-4	1	shoul be 2		2
9	1397943	Plastic	-2985	2985	4	29851397943Plastic-4	1	should be 2		2
10	1390957	ALi	2077,69	2077,69	1	2077,691390957ALi-1	2			2
11	1390957	ALi	-2077,69	2077,69	1	2077,691390957ALi-1	2			2
12	1390961	Univar Speciality Consumables Limited	1696,6	1696,6	1	1696,61390961Univar Speciality Consumables Limited-1	1			1
13	1390962	Felicia	565,64	565,64	1	565,641390962Felicia-1	1			1
14	1390963	Tele	678,19	678,19	1	678,191390963Tele -1	1			1
15	1390963	Tele	3049,36	3049,36	1	3049,361390963Tele -1	1			1
16	1390964	Remington	884,34	884,34	1	884,341390964Remington-1	1			1
17	123456	Plastic Reinforcement Fabrics Limited	2985	2985	5	2985123456Plastic Reinforcement Fabrics Limited-5	2			2
18	123456	Plastic Reinforcement Fabrics Limited	-2985	2985	5	2985123456Plastic Reinforcement Fabrics Limited-5	2			2
39	1397943	Plastic	2985	2985	6	29851397943Plastic-6	1	should be 2		2
Sheet1

Cell Formulas
Range	Formula
D2:D18,D39	D2	=ABS(C2)
E2:E18,E39	E2	=COUNTIF($C$2:C2,C2)
F2:F18,F39	F2	=CONCATENATE(D2,A2,B2,"-",E2)
G2:G18,G39	G2	=COUNTIF($F$2:$F$33515,F2)
J2:J18,J39	J2	=COUNTIFS($D$2:$D$39,D2,$A$2:$A$39,A2,$B$2:$B$39,B2)

my god i will try this in my file now i think this will work man! let me check on my file

 

[kingjet]

Perhaps something like this:

NEw check.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Invoice Separated	Customer Name	Sum of Revenue	ABS	Countif	Concat	Countif	Expected		New
2	1390951	Sahara	-2985	2985	1	29851390951Sahara-1	2			2
3	1390951	Sahara	2985	2985	1	29851390951Sahara-1	2			2
4	1393230	Sahara	-2985	2985	2	29851393230Sahara-2	2			2
5	1393230	Sahara	2985	2985	2	29851393230Sahara-2	2			2
6	1394846	Virgin	2985	2985	3	29851394846Virgin-3	1			1
7	12569	Plastic	-2985	2985	3	298512569Plastic-3	1	shoul be 2		2
8	12569	Plastic	2985	2985	4	298512569Plastic-4	1	shoul be 2		2
9	1397943	Plastic	-2985	2985	4	29851397943Plastic-4	1	should be 2		2
10	1390957	ALi	2077,69	2077,69	1	2077,691390957ALi-1	2			2
11	1390957	ALi	-2077,69	2077,69	1	2077,691390957ALi-1	2			2
12	1390961	Univar Speciality Consumables Limited	1696,6	1696,6	1	1696,61390961Univar Speciality Consumables Limited-1	1			1
13	1390962	Felicia	565,64	565,64	1	565,641390962Felicia-1	1			1
14	1390963	Tele	678,19	678,19	1	678,191390963Tele -1	1			1
15	1390963	Tele	3049,36	3049,36	1	3049,361390963Tele -1	1			1
16	1390964	Remington	884,34	884,34	1	884,341390964Remington-1	1			1
17	123456	Plastic Reinforcement Fabrics Limited	2985	2985	5	2985123456Plastic Reinforcement Fabrics Limited-5	2			2
18	123456	Plastic Reinforcement Fabrics Limited	-2985	2985	5	2985123456Plastic Reinforcement Fabrics Limited-5	2			2
39	1397943	Plastic	2985	2985	6	29851397943Plastic-6	1	should be 2		2
Sheet1

Cell Formulas
Range	Formula
D2:D18,D39	D2	=ABS(C2)
E2:E18,E39	E2	=COUNTIF($C$2:C2,C2)
F2:F18,F39	F2	=CONCATENATE(D2,A2,B2,"-",E2)
G2:G18,G39	G2	=COUNTIF($F$2:$F$33515,F2)
J2:J18,J39	J2	=COUNTIFS($D$2:$D$39,D2,$A$2:$A$39,A2,$B$2:$B$39,B2)

hello did not work in the last column j2 - am i supposed to get only 1 and 2 values or like random values?

 

[jorismoerings]

Hi,

You need to tell me more than “did not work”. The formula I provided was exactly in line with what you mentioned in the column with the “expected” results.
You can download my version of the test sheet in the other forum were you also posted this question.

 

[kingjet]

in my file in the last j2 where yours says 1 and 2 - i am getting numbers 1,2,3,4,15,16,18,21 - when i filter on the 2 i should get a contra but i am also getting two positive numbers that do not net off?

 

[kingjet]

Hi,

You need to tell me more than “did not work”. The formula I provided was exactly in line with what you mentioned in the column with the “expected” results.
You can download my version of the test sheet in the other forum were you also posted this question.

ok one second sorry i will post it correctly wait