Countifs formula maybe

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello experts

When one voucher has multiple taxes, the display is like this. With the help of a formula in a new column M, I have given a formula to show one voucher number with the total tax amount in one single line. After that I sort the data as per Tax and delete the blank cells. I do this manually every time. As the data is huge, it takes a lot of time. A formula would be very helpful to save time.

Book3
MNO
1FORMULAInvoice numberTax
2422.1WI-200012422.10
34401.9WI-2000144401.90
4548.82WI-200095548.82
5523.97WI-200117523.97
6342.63WI-200208342.63
7568.44WI-200228568.44
8415.2YP20P000150415.20
9214.4167157.41
106757.00
112368.221151575.00
12115793.22
1390.9619077.76
1419013.20
1522552391127.50
162391127.50
171597.743091543.50
1830954.24
19778290.00
2082977.00
211835.5538461833.17
2238462.38
23590.53862585.74
2438624.76
25151.453935114.84
26393536.61
271141.560011/20-210.00
280011/20-211141.56
292233.220116/20-210.00
300116/20-212233.22
31997.61015/BR/20000708743.44
32015/BR/20000708254.17
332750.130160/20-210.00
340160/20-212750.13
353068.50165/20-210.00
360165/20-213068.50
373705.910167/20-210.00
380167/20-213705.91
393908.880255/20-210.00
400255/20-213908.88
4147257.520351/20-210.00
420351/20-2147257.52
433247.560411/20-210.00
440411/20-213247.56
458004.910478/20-210.00
460478/20-218004.91
473111.530490/20-210.00
480490/20-213111.53
497101.90688/20-210.00
500688/20-217101.90
5128726.990773/20-210.00
520773/20-2128726.99
5336324.070797/20-210.00
540797/20-2136324.07
556822.90807/20-210.00
560807/20-216822.90
5743997.221131/20-210.00
581131/20-2143997.22
594188.381165/20-210.00
601165/20-214188.38
619669.871288/20-210.00
621288/20-219669.87
6323563.671378/20-210.00
641378/20-2123563.67
656771.831439/20-210.00
661439/20-216771.83
6719627.021494/20-210.00
681494/20-2119627.02
6914188.681585/20-210.00
701585/20-2114188.68
7113471.921632/20-210.00
721632/20-2113471.92
73986.58AIM-20-0480.00
74AIM-20-048986.58
7516963.99B/S/696/2021849.15
76B/S/696/202116114.84
77821.25BIS-BLR/2703225.00
78BIS-BLR/2703540.00
79BIS-BLR/270356.25
801072.8D2000020.00
81D2000021072.80
82560.7D2045570.00
83D204557560.70
8433.3D2047030.00
85D20470333.30
86872.14INC/2054302.70
87INC/2054569.44
882165.8INC/21161993.72
89INC/2116172.08
90742.95INC/2553342.00
91INC/2553400.95
921467INC/2608603.00
93INC/2608864.00
94705INC/262175.00
95INC/2621630.00
9670.5INC/262245.00
97INC/262225.50
98INC/26220.00
993237.36INC/27842914.20
100INC/2784323.16
1012366.99INC/2825987.29
102INC/28251379.70
1031003.07INC/2846597.00
104INC/2846406.07
1057128INMDTA2021/003601800.00
106INMDTA2021/003605328.00
10745930.58INMDTA2021/004403000.00
108INMDTA2021/0044042930.58
10936460.8INMDTA2021/007512250.00
110INMDTA2021/0075134210.80
11133562.8INMDTA2021/046751800.00
112INMDTA2021/0467531762.80
1133366.03OIM-20-008415.38
114OIM-20-0082950.65
115653.16PH-1498254.16
116PH-1498399.00
11711348.78PIA20017825.74
118PIA20017827683.03
119PIA20017823660.01
1209336.82PIA20050282163.13
121PIA20050287173.69
1225206.11SERVBAJV210004491535.38
123SERVBAJV210004493670.73
1241616.19SERVBAJV21001330770.28
125SERVBAJV21001330845.91
1262466.36SERVBAJV210025241361.23
127SERVBAJV210025241105.13
1286124.29SERVBAJV21003276772.13
129SERVBAJV210032765352.16
130SERVBAJV210032760.00
1311815.07SIKRMGL43870.43
132SIKRMGL43871814.64
Combine Multiple taxes
Cell Formulas
RangeFormula
M2:M8M2=O2
M9,M131,M126,M124,M122,M120,M115,M113,M111,M109,M107,M105,M103,M101,M99,M94,M92,M90,M88,M86,M84,M82,M80,M75,M73,M71,M69,M67,M65,M63,M61,M59,M57,M55,M53,M51,M49,M47,M45,M43,M41,M39,M37,M35,M33,M31,M29,M27,M25,M23,M21,M19,M17,M15,M13,M11M9=O9+O10
M77,M128,M117,M96M77=O77+O78+O79
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
RAJESH1960 said:
Hello experts

When one voucher has multiple taxes, the display is like this. With the help of a formula in a new column M, I have given a formula to show one voucher number with the total tax amount in one single line. After that I sort the data as per Tax and delete the blank cells. I do this manually every time. As the data is huge, it takes a lot of time. A formula would be very helpful to save time.

Book3
MNO
1FORMULAInvoice numberTax
2422.1WI-200012422.10
34401.9WI-2000144401.90
4548.82WI-200095548.82
5523.97WI-200117523.97
6342.63WI-200208342.63
7568.44WI-200228568.44
8415.2YP20P000150415.20
9214.4167157.41
106757.00
112368.221151575.00
12115793.22
1390.9619077.76
1419013.20
1522552391127.50
162391127.50
171597.743091543.50
1830954.24
19778290.00
2082977.00
211835.5538461833.17
2238462.38
23590.53862585.74
2438624.76
25151.453935114.84
26393536.61
271141.560011/20-210.00
280011/20-211141.56
292233.220116/20-210.00
300116/20-212233.22
31997.61015/BR/20000708743.44
32015/BR/20000708254.17
332750.130160/20-210.00
340160/20-212750.13
353068.50165/20-210.00
360165/20-213068.50
373705.910167/20-210.00
380167/20-213705.91
393908.880255/20-210.00
400255/20-213908.88
4147257.520351/20-210.00
420351/20-2147257.52
433247.560411/20-210.00
440411/20-213247.56
458004.910478/20-210.00
460478/20-218004.91
473111.530490/20-210.00
480490/20-213111.53
497101.90688/20-210.00
500688/20-217101.90
5128726.990773/20-210.00
520773/20-2128726.99
5336324.070797/20-210.00
540797/20-2136324.07
556822.90807/20-210.00
560807/20-216822.90
5743997.221131/20-210.00
581131/20-2143997.22
594188.381165/20-210.00
601165/20-214188.38
619669.871288/20-210.00
621288/20-219669.87
6323563.671378/20-210.00
641378/20-2123563.67
656771.831439/20-210.00
661439/20-216771.83
6719627.021494/20-210.00
681494/20-2119627.02
6914188.681585/20-210.00
701585/20-2114188.68
7113471.921632/20-210.00
721632/20-2113471.92
73986.58AIM-20-0480.00
74AIM-20-048986.58
7516963.99B/S/696/2021849.15
76B/S/696/202116114.84
77821.25BIS-BLR/2703225.00
78BIS-BLR/2703540.00
79BIS-BLR/270356.25
801072.8D2000020.00
81D2000021072.80
82560.7D2045570.00
83D204557560.70
8433.3D2047030.00
85D20470333.30
86872.14INC/2054302.70
87INC/2054569.44
882165.8INC/21161993.72
89INC/2116172.08
90742.95INC/2553342.00
91INC/2553400.95
921467INC/2608603.00
93INC/2608864.00
94705INC/262175.00
95INC/2621630.00
9670.5INC/262245.00
97INC/262225.50
98INC/26220.00
993237.36INC/27842914.20
100INC/2784323.16
1012366.99INC/2825987.29
102INC/28251379.70
1031003.07INC/2846597.00
104INC/2846406.07
1057128INMDTA2021/003601800.00
106INMDTA2021/003605328.00
10745930.58INMDTA2021/004403000.00
108INMDTA2021/0044042930.58
10936460.8INMDTA2021/007512250.00
110INMDTA2021/0075134210.80
11133562.8INMDTA2021/046751800.00
112INMDTA2021/0467531762.80
1133366.03OIM-20-008415.38
114OIM-20-0082950.65
115653.16PH-1498254.16
116PH-1498399.00
11711348.78PIA20017825.74
118PIA20017827683.03
119PIA20017823660.01
1209336.82PIA20050282163.13
121PIA20050287173.69
1225206.11SERVBAJV210004491535.38
123SERVBAJV210004493670.73
1241616.19SERVBAJV21001330770.28
125SERVBAJV21001330845.91
1262466.36SERVBAJV210025241361.23
127SERVBAJV210025241105.13
1286124.29SERVBAJV21003276772.13
129SERVBAJV210032765352.16
130SERVBAJV210032760.00
1311815.07SIKRMGL43870.43
132SIKRMGL43871814.64
Combine Multiple taxes
Cell Formulas
RangeFormula
M2:M8M2=O2
M9,M131,M126,M124,M122,M120,M115,M113,M111,M109,M107,M105,M103,M101,M99,M94,M92,M90,M88,M86,M84,M82,M80,M75,M73,M71,M69,M67,M65,M63,M61,M59,M57,M55,M53,M51,M49,M47,M45,M43,M41,M39,M37,M35,M33,M31,M29,M27,M25,M23,M21,M19,M17,M15,M13,M11M9=O9+O10
M77,M128,M117,M96M77=O77+O78+O79
Click to expand...
If there were only 2 common invoice numbers then this would have helped. =IF(N2<>N3,O2,O2+O3)
As there are more than 2 common invoice numbers I am unable to edit the formula as required. Another problem is I can't sort the blank cells, instead I have to use CF to color the common invoice numbers again.
 
Upvote 0
Try:

Book1 (version 1).xlsb
MNO
1FORMULAInvoice numberTax
2422.1WI-200012422.1
34401.9WI-2000144401.9
4548.82WI-200095548.82
5523.97WI-200117523.97
6342.63WI-200208342.63
7568.44WI-200228568.44
8415.2YP20P000150415.2
9214.4167157.41
10 6757
112368.221151575
12 115793.22
1390.9619077.76
14 19013.2
1522552391127.5
16 2391127.5
171597.743091543.5
18 30954.24
19778290
20 82977
211835.5538461833.17
22 38462.38
23590.53862585.74
24 38624.76
25151.453935114.84
26 393536.61
271141.560011/20-210
28 0011/20-211141.56
292233.220116/20-210
30 0116/20-212233.22
31997.61015/BR/20000708743.44
32 015/BR/20000708254.17
332750.130160/20-210
34 0160/20-212750.13
353068.50165/20-210
36 0165/20-213068.5
373705.910167/20-210
38 0167/20-213705.91
393908.880255/20-210
40 0255/20-213908.88
4147257.520351/20-210
42 0351/20-2147257.52
433247.560411/20-210
44 0411/20-213247.56
458004.910478/20-210
46 0478/20-218004.91
473111.530490/20-210
48 0490/20-213111.53
497101.90688/20-210
50 0688/20-217101.9
5128726.990773/20-210
52 0773/20-2128726.99
5336324.070797/20-210
54 0797/20-2136324.07
556822.90807/20-210
56 0807/20-216822.9
5743997.221131/20-210
58 1131/20-2143997.22
594188.381165/20-210
60 1165/20-214188.38
619669.871288/20-210
62 1288/20-219669.87
6323563.671378/20-210
64 1378/20-2123563.67
656771.831439/20-210
66 1439/20-216771.83
6719627.021494/20-210
68 1494/20-2119627.02
6914188.681585/20-210
70 1585/20-2114188.68
7113471.921632/20-210
72 1632/20-2113471.92
73986.58AIM-20-0480
74 AIM-20-048986.58
7516963.99B/S/696/2021849.15
76 B/S/696/202116114.84
77821.25BIS-BLR/2703225
78 BIS-BLR/2703540
79 BIS-BLR/270356.25
801072.8D2000020
81 D2000021072.8
82560.7D2045570
83 D204557560.7
8433.3D2047030
85 D20470333.3
86872.14INC/2054302.7
87 INC/2054569.44
882165.8INC/21161993.72
89 INC/2116172.08
90742.95INC/2553342
91 INC/2553400.95
921467INC/2608603
93 INC/2608864
94705INC/262175
95 INC/2621630
9670.5INC/262245
97 INC/262225.5
98 INC/26220
993237.36INC/27842914.2
100 INC/2784323.16
1012366.99INC/2825987.29
102 INC/28251379.7
1031003.07INC/2846597
104 INC/2846406.07
1057128INMDTA2021/003601800
106 INMDTA2021/003605328
10745930.58INMDTA2021/004403000
108 INMDTA2021/0044042930.58
10936460.8INMDTA2021/007512250
110 INMDTA2021/0075134210.8
11133562.8INMDTA2021/046751800
112 INMDTA2021/0467531762.8
1133366.03OIM-20-008415.38
114 OIM-20-0082950.65
115653.16PH-1498254.16
116 PH-1498399
11711348.78PIA20017825.74
118 PIA20017827683.03
119 PIA20017823660.01
1209336.82PIA20050282163.13
121 PIA20050287173.69
1225206.11SERVBAJV210004491535.38
123 SERVBAJV210004493670.73
1241616.19SERVBAJV21001330770.28
125 SERVBAJV21001330845.91
1262466.36SERVBAJV210025241361.23
127 SERVBAJV210025241105.13
1286124.29SERVBAJV21003276772.13
129 SERVBAJV210032765352.16
130 SERVBAJV210032760
1311815.07SIKRMGL43870.43
132 SIKRMGL43871814.64
Sheet16
Cell Formulas
RangeFormula
M2:M132M2=IF(COUNTIF(N$2:N2,N2)=1,SUMIF(N:N,N2,O:O),"")
 
Upvote 0
Eric W said:
Try:

Book1 (version 1).xlsb
MNO
1FORMULAInvoice numberTax
2422.1WI-200012422.1
34401.9WI-2000144401.9
4548.82WI-200095548.82
5523.97WI-200117523.97
6342.63WI-200208342.63
7568.44WI-200228568.44
8415.2YP20P000150415.2
9214.4167157.41
10 6757
112368.221151575
12 115793.22
1390.9619077.76
14 19013.2
1522552391127.5
16 2391127.5
171597.743091543.5
18 30954.24
19778290
20 82977
211835.5538461833.17
22 38462.38
23590.53862585.74
24 38624.76
25151.453935114.84
26 393536.61
271141.560011/20-210
28 0011/20-211141.56
292233.220116/20-210
30 0116/20-212233.22
31997.61015/BR/20000708743.44
32 015/BR/20000708254.17
332750.130160/20-210
34 0160/20-212750.13
353068.50165/20-210
36 0165/20-213068.5
373705.910167/20-210
38 0167/20-213705.91
393908.880255/20-210
40 0255/20-213908.88
4147257.520351/20-210
42 0351/20-2147257.52
433247.560411/20-210
44 0411/20-213247.56
458004.910478/20-210
46 0478/20-218004.91
473111.530490/20-210
48 0490/20-213111.53
497101.90688/20-210
50 0688/20-217101.9
5128726.990773/20-210
52 0773/20-2128726.99
5336324.070797/20-210
54 0797/20-2136324.07
556822.90807/20-210
56 0807/20-216822.9
5743997.221131/20-210
58 1131/20-2143997.22
594188.381165/20-210
60 1165/20-214188.38
619669.871288/20-210
62 1288/20-219669.87
6323563.671378/20-210
64 1378/20-2123563.67
656771.831439/20-210
66 1439/20-216771.83
6719627.021494/20-210
68 1494/20-2119627.02
6914188.681585/20-210
70 1585/20-2114188.68
7113471.921632/20-210
72 1632/20-2113471.92
73986.58AIM-20-0480
74 AIM-20-048986.58
7516963.99B/S/696/2021849.15
76 B/S/696/202116114.84
77821.25BIS-BLR/2703225
78 BIS-BLR/2703540
79 BIS-BLR/270356.25
801072.8D2000020
81 D2000021072.8
82560.7D2045570
83 D204557560.7
8433.3D2047030
85 D20470333.3
86872.14INC/2054302.7
87 INC/2054569.44
882165.8INC/21161993.72
89 INC/2116172.08
90742.95INC/2553342
91 INC/2553400.95
921467INC/2608603
93 INC/2608864
94705INC/262175
95 INC/2621630
9670.5INC/262245
97 INC/262225.5
98 INC/26220
993237.36INC/27842914.2
100 INC/2784323.16
1012366.99INC/2825987.29
102 INC/28251379.7
1031003.07INC/2846597
104 INC/2846406.07
1057128INMDTA2021/003601800
106 INMDTA2021/003605328
10745930.58INMDTA2021/004403000
108 INMDTA2021/0044042930.58
10936460.8INMDTA2021/007512250
110 INMDTA2021/0075134210.8
11133562.8INMDTA2021/046751800
112 INMDTA2021/0467531762.8
1133366.03OIM-20-008415.38
114 OIM-20-0082950.65
115653.16PH-1498254.16
116 PH-1498399
11711348.78PIA20017825.74
118 PIA20017827683.03
119 PIA20017823660.01
1209336.82PIA20050282163.13
121 PIA20050287173.69
1225206.11SERVBAJV210004491535.38
123 SERVBAJV210004493670.73
1241616.19SERVBAJV21001330770.28
125 SERVBAJV21001330845.91
1262466.36SERVBAJV210025241361.23
127 SERVBAJV210025241105.13
1286124.29SERVBAJV21003276772.13
129 SERVBAJV210032765352.16
130 SERVBAJV210032760
1311815.07SIKRMGL43870.43
132 SIKRMGL43871814.64
Sheet16
Cell Formulas
RangeFormula
M2:M132M2=IF(COUNTIF(N$2:N2,N2)=1,SUMIF(N:N,N2,O:O),"")
Click to expand...
It's Perfect. Thanks Eric W. You just saved a lot of my time.
 
Upvote 0
Eric W. Your formula is working only if the invoice numbers are different. A few traders have the same invoice number. So, when I ran the formula in the data base, I got so many errors. So, I have sent an additional column which is the name of the trade. Please help me by editing the formula accordingly. The green colored cells are the multiple taxes in one voucher which I derived from Conditional formatting.

Book2
ABCDEFGHIJK
1TINInvoice numberFormulaCentral Tax (₹)
21Customer 1219200.00
32Customer 2353831.6831.60
43Customer 3107720720.00
54Customer 4108720720.00
65Customer 51002484.94540.00
76Customer 6101540540.00
87Customer 7102540540.00
98Customer 8502727.00
109Customer 999112.5112.50
1110Customer 10111530.91530.90
1211Customer 11605472.68472.68
1312Customer 12606277.2277.20
1413Customer 13618477477.00
1514Customer 14621461.7461.70
1615Customer 1562493.693.60
1716Customer 16628235.8235.80
1817Customer 171745957.5827.00
1918Customer 1826171171.00
2019Customer 19321402.21185.40
2120Customer 1932 1216.81
2221Customer 213942685.591127.19
2322Customer 22INC/2050615615.00
2423Customer 23100 302.70
2524Customer 23100 569.44
2625Customer 2545189163000.00
2726Customer 26100 0.00
2827Customer 26100 1072.80
2928Customer 28110388.521800.00
3029Customer 2939 5328.00
3130Customer 2939 36230.40
3231Customer 3117 3000.00
3332Customer 3117 42930.58
3433Customer 33449684968.00
3534Customer 34533917.0433917.04
3635Customer 35634810.5634810.56
3736Customer 36736460.82250.00
3837Customer 367 34210.80
3938Customer 381 0.00
4039Customer 39200.00
4140Customer 401 8588.52
B2B Seperate
Cell Formulas
RangeFormula
J2:J41J2=IF(COUNTIF(E$2:E2,E2)=1,SUMIF(E:E,E2,K:K),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E41Expression=COUNTIFS(C$2:C$20000,C2,E$2:E$20000,E2)>1textNO
E2:E41Expression=COUNTIFS(D$2:D$1835,D2,E$2:E$1835,E2)>1textNO

It was my mistake for misunderstanding that the invoice numbers will be different.
 
Upvote 0
It should be:

Excel Formula: 
=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(K:K,C:C,C2,E:E,E2),"")
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

S
  • Solved
need to add + in diff. and
Replies
5
Views
312
Peter_SSs
Peter_SSs
U
  • Question
VBA Error: paste method of worksheet class failed 1004
Replies
3
Views
690
Fluff
Fluff
L
  • Question
PasteSpecial value . . . . very urgent
2
Replies
13
Views
1K
Legacy 246103
L
P
  • Question
Procedure too long - help!!
Replies
3
Views
629
philscrase
P
S
  • Question
Copying a range of cells and pasting into one.
2
Replies
17
Views
2K
Ste_Moore01
S

Forum statistics

Threads
1,214,870
Messages
6,122,019
Members
449,060
Latest member
LinusJE

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top