Find percentage of reconciliation

[askall1000]

Thank you in advance. I want to find the percentage of completed reconciliation in F2 cell, this is only part of whole data, my table goes 100, 102, 120 etc (not serial). There are also sub accounts; I am trying to do when I enter account code to F1, F2 will show me how much is completed base on reference column with Ref* and main account e.g 102.

sil01.xlsx
	A	B	C	D	E	F
1					Account Code	100
2					Percentage of complete
3	Account Code	Total	Reference
4	100	92.789,69
5	100 01	55.800,00
6	100 01 001	55.800,00
7	100 02	36.989,69
8	100 02 001	4.222,70
9	100 02 002	32.766,99
10	102	4.046.887,96
11	102 01	90.311,81
12	102 01 002	43.606,48	RefB003
13	102 01 003	7.503,73	RefB002
14	102 01 005	28,44	RefB004
15	102 01 006	6,35	x
16	102 01 007	39.166,81	RefB001
17	102 02	0,00
18	102 02 001	0,00
19	102 02 002	0,00
20	102 03	3.956.576,15
21	102 03 001	2.885.970,19	RefB003
22	102 03 002	532.722,97	RefB003
23	102 03 003	537.882,99	RefB003
Sheet1

 

[jasonb75]

See if this does what you need

Book1
	A	B	C	D	E	F
1					Account Code	102
2					Percentage of complete	50%
3	Account Code	Total	Reference
4	100	92,789.69
5	100 01	55,800.00
6	100 01 001	55,800.00
7	100 02	36,989.69
8	100 02 001	4,222.70
9	100 02 002	32,766.99
10	102	4,046,887.96
11	102 01	90,311.81
12	102 01 002	43,606.48	RefB003
13	102 01 003	7,503.73	RefB002
14	102 01 005	28.44	RefB004
15	102 01 006	6.35	x
16	102 01 007	39,166.81	RefB001
17	102 02	-
18	102 02 001	-
19	102 02 002	-
20	102 03	3,956,576.15
21	102 03 001	2,885,970.19	RefB003
22	102 03 002	532,722.97	RefB003
23	102 03 003	537,882.99	RefB003
Sheet3

Cell Formulas
Range	Formula
F2	F2	=SUM(COUNTIFS($C$4:$C$23,"Ref*",A4:A23,CHOOSE({1,2},F1,F1&"*")))/SUM(COUNTIF(A4:A23,CHOOSE({1,2},F1,F1&"*")))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[askall1000]

See if this does what you need

Book1
	A	B	C	D	E	F
1					Account Code	102
2					Percentage of complete	50%
3	Account Code	Total	Reference
4	100	92,789.69
5	100 01	55,800.00
6	100 01 001	55,800.00
7	100 02	36,989.69
8	100 02 001	4,222.70
9	100 02 002	32,766.99
10	102	4,046,887.96
11	102 01	90,311.81
12	102 01 002	43,606.48	RefB003
13	102 01 003	7,503.73	RefB002
14	102 01 005	28.44	RefB004
15	102 01 006	6.35	x
16	102 01 007	39,166.81	RefB001
17	102 02	-
18	102 02 001	-
19	102 02 002	-
20	102 03	3,956,576.15
21	102 03 001	2,885,970.19	RefB003
22	102 03 002	532,722.97	RefB003
23	102 03 003	537,882.99	RefB003
Sheet3

Cell Formulas
Range	Formula
F2	F2	=SUM(COUNTIFS($C$4:$C$23,"Ref*",A4:A23,CHOOSE({1,2},F1,F1&"*")))/SUM(COUNTIF(A4:A23,CHOOSE({1,2},F1,F1&"*")))
Press CTRL+SHIFT+ENTER to enter array formulas.

thank you very much for your time, but maybe i couldn't explain clearly, as a numeric example my result should be like this for 102 account: sum(b12:b14;b16;b21:b23)/b10. when i changed 102 to 100 or other account it must be sum all numbers if there is ref* and divide it with main account.

 

[jasonb75]

sum(b12:b14;b16;b21:b23)/b10

Why is B13 excluded?

Assuming that is should be included, try

=SUMIFS($B$4:$B$23,$C$4:$C$23,"Ref*",$A$4:$A$23,F1&"*")/VLOOKUP(F1,$A$4:$B$23,2,0)

 

[askall1000]

Why is B13 excluded?

Assuming that is should be included, try

=SUMIFS($B$4:$B$23,$C$4:$C$23,"Ref*",$A$4:$A$23,F1&"*")/VLOOKUP(F1,$A$4:$B$23,2,0)

It is included and thank you very much, it is what I was looking. When I enter f1 as a value it gave error i needed to enter as '102 is there alternative when i enter value also recognize as text or vice versa to the range.

 

[jasonb75]

If the main account numbers in the table could be text then it will be problematic. With F1 being text, you could simply coerce it with

=SUMIFS($B$4:$B$23,$C$4:$C$23,"Ref*",$A$4:$A$23,F1&"*")/VLOOKUP(--F1,$A$4:$B$23,2,0)

but it would be more logical to avoid using text there to begin with.

 

[askall1000]

If the main account numbers in the table could be text then it will be problematic. With F1 being text, you could simply coerce it with

=SUMIFS($B$4:$B$23,$C$4:$C$23,"Ref*",$A$4:$A$23,F1&"*")/VLOOKUP(--F1,$A$4:$B$23,2,0)

but it would be more logical to avoid using text there to begin with.

Unfortunately, I pull the data from software and main accounts in table is given text but it is not a big deal i could enter as a text to f1. Thank you again for your help.