Sum total when name changes

M

Marq1995

New Member
Joined
Mar 22, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I need you to help me.. Im loosing sleep! haha

What I want to do is sum up the debit and credit column and add the total in the blank row (when the name value changes). But, if the value is <0 I want the total to go in the debit column - if it is >0 I want it to go in the credit column

I hope that makes sense...

Please help!!

I
For MrExcel.JPG
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Welcome to the MrExcel board!

Any chance you could give us the sample data again but this time with XL2BB and include the expected results (entered manually)?
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

Any chance you could give us the sample data again but this time with XL2BB and include the expected results (entered manually)?
Click to expand...
Journal Template.xlsm
ABCD
1NameCurrencyDebitCredit
2A ISAACGBP1-1
3A ISAACGBP2-1
4A ISAACGBP3-1
5A ISAACGBP4-1
6A ISAACGBP5-1
7A ISAACGBP6-1
8A ISAACGBP7-1
9A ISAACGBP8-1
10A ISAACGBP9-1
11A ISAACGBP10-1
12A ISAACGBP11-1
13A ISAACGBP12-1
14A ISAACGBP13-1
15A ISAACGBP14-1
16A ISAACGBP15-1
17A ISAACGBP16-1
18A ISAACGBP17-1
19A ISAACGBP18-1
20A ISAACGBP19-1
21GBP171
22A JACKSONGBP20-1
23A JACKSONGBP21-1
24A JACKSONGBP22-1
25A JACKSONGBP23-1
26A JACKSONGBP24-1
27A JACKSONGBP25-1
28A JACKSONGBP26-1
29A JACKSONGBP27-1
30A JACKSONGBP28-1
31A JACKSONGBP29-1
32GBP235
33A MELROSEGBP30-1
34A MELROSEGBP31-1
35A MELROSEGBP32-1
36A MELROSEGBP33-1
37A MELROSEGBP34-1
38A MELROSEGBP35-1
39A MELROSEGBP36-1
40A MELROSEGBP37-1
41A MELROSEGBP38-1
42A MELROSEGBP39-1
43GBP335
44B PAVEYGBP40-1
45B PAVEYGBP41-1
46B PAVEYGBP42-1
47B PAVEYGBP43-1
48B PAVEYGBP44-1
49B PAVEYGBP45-1
50B PAVEYGBP46-1
51B PAVEYGBP47-1
52B PAVEYGBP48-1
53B PAVEYGBP49-1
54B PAVEYGBP50-1
55B PAVEYGBP51-1
56B PAVEYGBP52-1
57B PAVEYGBP53-1
58B PAVEYGBP54-1
59B PAVEYGBP55-1
60B PAVEYGBP56-1
61B PAVEYGBP57-1
62B PAVEYGBP58-1
63GBP912
64C AMESGBP59-1
65C AMESGBP60-1
66C AMESGBP61-1
67C AMESGBP62-1
68C AMESGBP63-1
69GBP300
70C BAILEYGBP64-1
71C BAILEYGBP65-1
72C BAILEYGBP66-1
73C BAILEYGBP67-1
74C BAILEYGBP68-1
75C BAILEYGBP69-1
76C BAILEYGBP70-1
77C BAILEYGBP71-1
78C BAILEYGBP72-1
79C BAILEYGBP73-1
80C BAILEYGBP74-1
81C BAILEYGBP75-1
82C BAILEYGBP76-1
83C BAILEYGBP77-1
84C BAILEYGBP78-1
85C BAILEYGBP79-1
86C BAILEYGBP80-1
87C BAILEYGBP81-1
88C BAILEYGBP82-1
89C BAILEYGBP83-1
90C BAILEYGBP84-1
91C BAILEYGBP85-1
92C BAILEYGBP86-1
93C BAILEYGBP87-1
94C BAILEYGBP88-1
95C BAILEYGBP89-1
96C BAILEYGBP90-1
97C BAILEYGBP91-1
98C BAILEYGBP92-1
99C BAILEYGBP93-1
100C BAILEYGBP94-1
101C BAILEYGBP95-1
102C BAILEYGBP96-1
103C BAILEYGBP97-1
104C BAILEYGBP98-1
105C BAILEYGBP99-1
106C BAILEYGBP100-1
107C BAILEYGBP101-1
108C BAILEYGBP102-1
109C BAILEYGBP103-1
110C BAILEYGBP104-1
111C BAILEYGBP105-1
112C BAILEYGBP106-1
113C BAILEYGBP107-1
114C BAILEYGBP108-1
115C BAILEYGBP109-1
116C BAILEYGBP110-1
117C BAILEYGBP111-1
118C BAILEYGBP112-1
119C BAILEYGBP113-1
120C BAILEYGBP114-1
121C BAILEYGBP115-1
122C BAILEYGBP116-1
123C BAILEYGBP117-1
124C BAILEYGBP118-1
125C BAILEYGBP119-1
126C BAILEYGBP120-1
127C BAILEYGBP121-1
128C BAILEYGBP122-1
129GBP5428
130C LLOYDGBP123-1
131C LLOYDGBP124-1
132C LLOYDGBP125-1
133C LLOYDGBP126-1
134GBP494
135C M MCLELLANDGBP127-1
136GBP126
137C MOSSGBP128-1
138C MOSSGBP129-1
139C MOSSGBP130-1
140C MOSSGBP131-1
141C MOSSGBP132-1
142C MOSSGBP133-1
143C MOSSGBP134-1
144C MOSSGBP135-1
145C MOSSGBP136-1
146C MOSSGBP137-1
147GBP1315
148D BALDWINGBP138-1
149D BALDWINGBP139-1
150D BALDWINGBP140-1
151D BALDWINGBP141-1
152D BALDWINGBP142-1
153D BALDWINGBP143-1
154D BALDWINGBP144-1
155D BALDWINGBP145-1
156D BALDWINGBP146-1
157D BALDWINGBP147-1
158D BALDWINGBP148-1
159D BALDWINGBP149-1
160D BALDWINGBP150-1
161D BALDWINGBP151-1
162D BALDWINGBP152-1
163D BALDWINGBP153-1
164GBP2312
165D CHAPMAN-HUNTGBP154-1
166D CHAPMAN-HUNTGBP155-1
167D CHAPMAN-HUNTGBP156-1
168D CHAPMAN-HUNTGBP157-1
169D CHAPMAN-HUNTGBP158-1
170D CHAPMAN-HUNTGBP159-1
171D CHAPMAN-HUNTGBP160-1
172D CHAPMAN-HUNTGBP161-1
173D CHAPMAN-HUNTGBP162-1
174D CHAPMAN-HUNTGBP163-1
175D CHAPMAN-HUNTGBP164-1
176D CHAPMAN-HUNTGBP165-1
177D CHAPMAN-HUNTGBP166-1
178D CHAPMAN-HUNTGBP167-1
179D CHAPMAN-HUNTGBP168-1
180D CHAPMAN-HUNTGBP169-1
181D CHAPMAN-HUNTGBP170-1
182D CHAPMAN-HUNTGBP171-1
183D CHAPMAN-HUNTGBP172-1
184D CHAPMAN-HUNTGBP173-1
185D CHAPMAN-HUNTGBP174-1
186D CHAPMAN-HUNTGBP175-1
187D CHAPMAN-HUNTGBP176-1
188D CHAPMAN-HUNTGBP177-1
189D CHAPMAN-HUNTGBP178-1
190D CHAPMAN-HUNTGBP179-1
191D CHAPMAN-HUNTGBP180-1
192D CHAPMAN-HUNTGBP181-1
193GBP4662
194D ONEILGBP182-1
195D ONEILGBP183-1
196GBP363
197D RAMSAYGBP184-1
198D RAMSAYGBP185-1
199D RAMSAYGBP186-1
200GBP552
201D TRILLGBP187-1
202GBP186
203E LOFTING-KISAKYEGBP188-1
204E LOFTING-KISAKYEGBP189-1
205E LOFTING-KISAKYEGBP190-1
206GBP564
207G MAKWANAGBP191-1
208G MAKWANAGBP192-1
209G MAKWANAGBP193-1
210GBP573
211H MCMANUSGBP194-1
212H MCMANUSGBP195-1
213H MCMANUSGBP196-1
214H MCMANUSGBP197-1
215GBP778
216I THOMASGBP198-1
217I THOMASGBP199-1
218I THOMASGBP200-1
219I THOMASGBP201-1
220I THOMASGBP202-1
221I THOMASGBP203-1
222I THOMASGBP204-1
223I THOMASGBP205-1
224I THOMASGBP206-1
225I THOMASGBP207-1
226I THOMASGBP208-1
227I THOMASGBP209-1
228I THOMASGBP210-1
229I THOMASGBP211-1
230I THOMASGBP212-1
231I THOMASGBP213-1
232I THOMASGBP214-1
233I THOMASGBP215-1
234I THOMASGBP216-1
235I THOMASGBP217-1
236I THOMASGBP218-1
237I THOMASGBP219-1
238I THOMASGBP220-1
239I THOMASGBP221-1
240I THOMASGBP222-1
241I THOMASGBP223-1
242I THOMASGBP224-1
243I THOMASGBP225-1
244GBP5894
245I THORPEGBP226-1
246I THORPEGBP227-1
247I THORPEGBP228-1
248GBP678
249I XENITIDESGBP229-1
250I XENITIDESGBP230-1
251I XENITIDESGBP231-1
252I XENITIDESGBP232-1
253I XENITIDESGBP233-1
254I XENITIDESGBP234-1
255I XENITIDESGBP235-1
256I XENITIDESGBP236-1
257I XENITIDESGBP237-1
258I XENITIDESGBP238-1
259I XENITIDESGBP239-1
260I XENITIDESGBP240-1
261I XENITIDESGBP241-1
262I XENITIDESGBP242-1
263I XENITIDESGBP243-1
264I XENITIDESGBP244-1
265I XENITIDESGBP245-1
266I XENITIDESGBP246-1
267GBP4257
268J CARROLLGBP247-1
269J CARROLLGBP248-1
270J CARROLLGBP249-1
271J CARROLLGBP250-1
272J CARROLLGBP251-1
273J CARROLLGBP252-1
274J CARROLLGBP253-1
275J CARROLLGBP254-1
276J CARROLLGBP255-1
277J CARROLLGBP256-1
278J CARROLLGBP257-1
279J CARROLLGBP258-1
280J CARROLLGBP259-1
281J CARROLLGBP260-1
282J CARROLLGBP261-1
283J CARROLLGBP262-1
284J CARROLLGBP263-1
285GBP4318
286J GREENGBP264-1
287J GREENGBP265-1
288J GREENGBP266-1
289J GREENGBP267-1
290J GREENGBP268-1
291J GREENGBP269-1
292GBP1593
293J JONESGBP270-1
294J JONESGBP271-1
295J JONESGBP272-1
296J JONESGBP273-1
297J JONESGBP274-1
298J JONESGBP275-1
299J JONESGBP276-1
300J JONESGBP277-1
301J JONESGBP278-1
302GBP2457
303J MACPHERSONGBP279-1
304J MACPHERSONGBP280-1
305J MACPHERSONGBP281-1
306J MACPHERSONGBP282-1
307J MACPHERSONGBP283-1
308J MACPHERSONGBP284-1
309J MACPHERSONGBP285-1
310J MACPHERSONGBP286-1
311J MACPHERSONGBP287-1
312J MACPHERSONGBP288-1
313J MACPHERSONGBP289-1
314J MACPHERSONGBP290-1
315J MACPHERSONGBP291-1
316J MACPHERSONGBP292-1
317J MACPHERSONGBP293-1
318J MACPHERSONGBP294-1
319J MACPHERSONGBP295-1
320J MACPHERSONGBP296-1
321J MACPHERSONGBP297-1
322J MACPHERSONGBP298-1
323J MACPHERSONGBP299-1
324J MACPHERSONGBP300-1
325GBP6347
326J STENNINGGBP301-1
327J STENNINGGBP302-1
328J STENNINGGBP303-1
329J STENNINGGBP304-1
330J STENNINGGBP305-1
331J STENNINGGBP306-1
332GBP1815
333K BIRCHGBP307-1
334K BIRCHGBP308-1
335K BIRCHGBP309-1
336K BIRCHGBP310-1
337K BIRCHGBP311-1
338K BIRCHGBP312-1
339K BIRCHGBP313-1
340K BIRCHGBP314-1
341K BIRCHGBP315-1
342K BIRCHGBP316-1
343K BIRCHGBP317-1
344K BIRCHGBP318-1
345K BIRCHGBP319-1
346K BIRCHGBP320-1
347K BIRCHGBP321-1
348K BIRCHGBP322-1
349K BIRCHGBP323-1
350K BIRCHGBP324-1
351K BIRCHGBP325-1
352K BIRCHGBP326-1
353GBP6310
354K FLOWERGBP327-1
355K FLOWERGBP328-1
356K FLOWERGBP329-1
357K FLOWERGBP330-1
358K FLOWERGBP331-1
359K FLOWERGBP332-1
360K FLOWERGBP333-1
361K FLOWERGBP334-1
362GBP2636
363K GREENGBP335-1
364K GREENGBP336-1
365K GREENGBP337-1
366K GREENGBP338-1
367K GREENGBP339-1
368K GREENGBP340-1
369K GREENGBP341-1
370GBP2359
371M FILKINSGBP342-1
372M FILKINSGBP343-1
373M FILKINSGBP344-1
374M FILKINSGBP345-1
375M FILKINSGBP346-1
376M FILKINSGBP347-1
377M FILKINSGBP348-1
378M FILKINSGBP349-1
379M FILKINSGBP350-1
380M FILKINSGBP351-1
381M FILKINSGBP352-1
382M FILKINSGBP353-1
383M FILKINSGBP354-1
384M FILKINSGBP355-1
385M FILKINSGBP356-1
386GBP5220
387M RAJANIGBP357-1
388GBP356
389N MCGINLEYGBP358-1
390N MCGINLEYGBP359-1
391N MCGINLEYGBP360-1
392N MCGINLEYGBP361-1
393N MCGINLEYGBP362-1
394GBP1795
395P HORWOODGBP363-1
396P HORWOODGBP364-1
397P HORWOODGBP365-1
398P HORWOODGBP366-1
399GBP1454
400P JEFFREYGBP367-1
401P JEFFREYGBP368-1
402GBP733
403P LINDSAYGBP369-1
404P LINDSAYGBP370-1
405P LINDSAYGBP371-1
406P LINDSAYGBP372-1
407P LINDSAYGBP373-1
408P LINDSAYGBP374-1
409P LINDSAYGBP375-1
410P LINDSAYGBP376-1
411P LINDSAYGBP377-1
412P LINDSAYGBP378-1
413P LINDSAYGBP379-1
414P LINDSAYGBP380-1
415P LINDSAYGBP381-1
416P LINDSAYGBP382-1
417P LINDSAYGBP383-1
418P LINDSAYGBP384-1
419P LINDSAYGBP385-1
420P LINDSAYGBP386-1
421P LINDSAYGBP387-1
422P LINDSAYGBP388-1
423P LINDSAYGBP389-1
424P LINDSAYGBP390-1
425P LINDSAYGBP391-1
426P LINDSAYGBP392-1
427GBP9108
428P WOODGBP393-1
429P WOODGBP394-1
430GBP785
431R FERRIMANGBP395-1
432R FERRIMANGBP396-1
433R FERRIMANGBP397-1
434R FERRIMANGBP398-1
435R FERRIMANGBP399-1
436R FERRIMANGBP400-1
437R FERRIMANGBP401-1
438R FERRIMANGBP402-1
439R FERRIMANGBP403-1
440GBP3582
441R FORBES-HARRISSGBP404-1
442GBP403
443R GIBSONGBP405-1
444R GIBSONGBP406-1
445R GIBSONGBP407-1
446R GIBSONGBP408-1
447R GIBSONGBP409-1
448R GIBSONGBP410-1
449R GIBSONGBP411-1
450R GIBSONGBP412-1
451R GIBSONGBP413-1
452GBP3672
453R PATELGBP414-1
454R PATELGBP415-1
455R PATELGBP416-1
456R PATELGBP417-1
457R PATELGBP418-1
458R PATELGBP419-1
459R PATELGBP420-1
460R PATELGBP421-1
461R PATELGBP422-1
462GBP3753
463S A LEYSHONGBP423-1
464S A LEYSHONGBP424-1
465S A LEYSHONGBP425-1
466S A LEYSHONGBP426-1
467S A LEYSHONGBP427-1
468GBP2120
469S CAMPBELLGBP428-1
470S CAMPBELLGBP429-1
471S CAMPBELLGBP430-1
472S CAMPBELLGBP431-1
473S CAMPBELLGBP432-1
474S CAMPBELLGBP433-1
475S CAMPBELLGBP434-1
476S CAMPBELLGBP435-1
477S CAMPBELLGBP436-1
478S CAMPBELLGBP437-1
479S CAMPBELLGBP438-1
480S CAMPBELLGBP439-1
481S CAMPBELLGBP440-1
482S CAMPBELLGBP441-1
483GBP6069
484S DOHERTYGBP442-1
485S DOHERTYGBP443-1
486S DOHERTYGBP444-1
487S DOHERTYGBP445-1
488S DOHERTYGBP446-1
489S DOHERTYGBP447-1
490S DOHERTYGBP448-1
491S DOHERTYGBP449-1
492S DOHERTYGBP450-1
493S DOHERTYGBP451-1
494S DOHERTYGBP452-1
495S DOHERTYGBP453-1
496S DOHERTYGBP454-1
497S DOHERTYGBP455-1
498S DOHERTYGBP456-1
499S DOHERTYGBP457-1
500S DOHERTYGBP458-1
501S DOHERTYGBP459-1
502S DOHERTYGBP460-1
503S DOHERTYGBP461-1
504S DOHERTYGBP462-1
505GBP9471
506S HARWOODGBP463-1
507S HARWOODGBP464-1
508S HARWOODGBP465-1
509S HARWOODGBP466-1
510S HARWOODGBP467-1
511S HARWOODGBP468-1
512S HARWOODGBP469-1
513S HARWOODGBP470-1
514S HARWOODGBP471-1
515S HARWOODGBP472-1
516S HARWOODGBP473-1
517S HARWOODGBP474-1
518S HARWOODGBP475-1
519GBP6084
520S MCGRATHGBP476-1
521GBP475
522S RIDDLEGBP477-1
523S RIDDLEGBP478-1
524S RIDDLEGBP479-1
525S RIDDLEGBP480-1
526S RIDDLEGBP481-1
527S RIDDLEGBP482-1
528GBP2871
529T BROGAN-SHAWGBP483-1
530T BROGAN-SHAWGBP484-1
531T BROGAN-SHAWGBP485-1
532T BROGAN-SHAWGBP486-1
533GBP1934
534T BULLIMOREGBP487-1
535T BULLIMOREGBP488-1
536T BULLIMOREGBP489-1
537T BULLIMOREGBP490-1
538T BULLIMOREGBP491-1
539T BULLIMOREGBP492-1
540GBP2931
541V CONLANGBP493-1
542GBP492
Journal Template
Cell Formulas
RangeFormula
C21C21=SUM($C$2:$D$20)
C32C32=SUM($C$22:$D$31)
C43C43=SUM($C$33:$D$42)
C63C63=SUM($C$44:$D$62)
C69C69=SUM($C$64:$D$68)
C129C129=SUM($C$70:$D$128)
C134C134=SUM($C$130:$D$133)
C136C136=SUM($C$135:$D$135)
C147C147=SUM($C$137:$D$146)
C164C164=SUM($C$148:$D$163)
C193C193=SUM($C$165:$D$192)
C196C196=SUM($C$194:$D$195)
C200C200=SUM($C$197:$D$199)
C202C202=SUM($C$201:$D$201)
C206C206=SUM($C$203:$D$205)
C210C210=SUM($C$207:$D$209)
C215C215=SUM($C$211:$D$214)
C244C244=SUM($C$216:$D$243)
C248C248=SUM($C$245:$D$247)
C267C267=SUM($C$249:$D$266)
C285C285=SUM($C$268:$D$284)
C292C292=SUM($C$286:$D$291)
C302C302=SUM($C$293:$D$301)
C325C325=SUM($C$303:$D$324)
C332C332=SUM($C$326:$D$331)
C353C353=SUM($C$333:$D$352)
C362C362=SUM($C$354:$D$361)
C370C370=SUM($C$363:$D$369)
C386C386=SUM($C$371:$D$385)
C388C388=SUM($C$387:$D$387)
C394C394=SUM($C$389:$D$393)
C399C399=SUM($C$395:$D$398)
C402C402=SUM($C$400:$D$401)
C427C427=SUM($C$403:$D$426)
C430C430=SUM($C$428:$D$429)
C440C440=SUM($C$431:$D$439)
C442C442=SUM($C$441:$D$441)
C452C452=SUM($C$443:$D$451)
C462C462=SUM($C$453:$D$461)
C468C468=SUM($C$463:$D$467)
C483C483=SUM($C$469:$D$482)
C505C505=SUM($C$484:$D$504)
C519C519=SUM($C$506:$D$518)
C521C521=SUM($C$520:$D$520)
C528C528=SUM($C$522:$D$527)
C533C533=SUM($C$529:$D$532)
C540C540=SUM($C$534:$D$539)
C542C542=SUM($C$541:$D$541)
 
Upvote 0
Sorry - ignore the previous one...

I have added the expected results manually -

Journal Template.xlsm
ABCD
1NameCurrencyDebitCredit
2A ISAACGBP1-2
3A ISAACGBP2-3
4A ISAACGBP3-4
5A ISAACGBP4-5
6A ISAACGBP5-6
7A ISAACGBP6-7
8A ISAACGBP7-8
9A ISAACGBP8-9
10A ISAACGBP9-10
11A ISAACGBP10-11
12A ISAACGBP11-12
13A ISAACGBP12-13
14A ISAACGBP13-14
15A ISAACGBP14-15
16A ISAACGBP15-16
17A ISAACGBP16-17
18A ISAACGBP17-18
19A ISAACGBP18-19
20A ISAACGBP19-20
21GBP-19
22A JACKSONGBP20-1
23A JACKSONGBP21-1
24A JACKSONGBP22-1
25A JACKSONGBP23-1
26A JACKSONGBP24-1
27A JACKSONGBP25-1
28A JACKSONGBP26-1
29A JACKSONGBP27-1
30A JACKSONGBP28-1
31A JACKSONGBP29-1
32GBP235
33A MELROSEGBP30-35
34A MELROSEGBP31-36
35A MELROSEGBP32-37
36A MELROSEGBP33-38
37A MELROSEGBP34-39
38A MELROSEGBP35-40
39A MELROSEGBP36-41
40A MELROSEGBP37-42
41A MELROSEGBP38-43
42A MELROSEGBP39-44
43GBP-50
44B PAVEYGBP4045
45B PAVEYGBP4146
46B PAVEYGBP4247
47B PAVEYGBP4348
48B PAVEYGBP4449
49B PAVEYGBP4550
50B PAVEYGBP4651
51B PAVEYGBP4752
52B PAVEYGBP4853
53B PAVEYGBP4954
54B PAVEYGBP5055
55B PAVEYGBP5156
56B PAVEYGBP5257
57B PAVEYGBP5358
58B PAVEYGBP5459
59B PAVEYGBP5560
60B PAVEYGBP5661
61B PAVEYGBP5762
62B PAVEYGBP5863
63GBP1957
Journal Template
Cell Formulas
RangeFormula
C21C21=SUM($C$2:$D$20)
D32D32=SUM($C$22:$D$31)
C43C43=SUM($C$33:$D$42)
C63C63=SUM($C$44:$D$62)
 
Upvote 0
Looks like that last sum is in the wrong column?

Is this what you mean, assuming that you have the LET function?

21 03 22.xlsm
ABCD
1NameCurrencyDebitCredit
2A ISAACGBP1-2
3A ISAACGBP2-3
4A ISAACGBP3-4
5A ISAACGBP4-5
6A ISAACGBP5-6
7A ISAACGBP6-7
8A ISAACGBP7-8
9A ISAACGBP8-9
10A ISAACGBP9-10
11A ISAACGBP10-11
12A ISAACGBP11-12
13A ISAACGBP12-13
14A ISAACGBP13-14
15A ISAACGBP14-15
16A ISAACGBP15-16
17A ISAACGBP16-17
18A ISAACGBP17-18
19A ISAACGBP18-19
20A ISAACGBP19-20
21GBP-19 
22A JACKSONGBP20-1
23A JACKSONGBP21-1
24A JACKSONGBP22-1
25A JACKSONGBP23-1
26A JACKSONGBP24-1
27A JACKSONGBP25-1
28A JACKSONGBP26-1
29A JACKSONGBP27-1
30A JACKSONGBP28-1
31A JACKSONGBP29-1
32GBP 235
33A MELROSEGBP30-35
34A MELROSEGBP31-36
35A MELROSEGBP32-37
36A MELROSEGBP33-38
37A MELROSEGBP34-39
38A MELROSEGBP35-40
39A MELROSEGBP36-41
40A MELROSEGBP37-42
41A MELROSEGBP38-43
42A MELROSEGBP39-44
43GBP-50 
44B PAVEYGBP4045
45B PAVEYGBP4146
46B PAVEYGBP4247
47B PAVEYGBP4348
48B PAVEYGBP4449
49B PAVEYGBP4550
50B PAVEYGBP4651
51B PAVEYGBP4752
52B PAVEYGBP4853
53B PAVEYGBP4954
54B PAVEYGBP5055
55B PAVEYGBP5156
56B PAVEYGBP5257
57B PAVEYGBP5358
58B PAVEYGBP5459
59B PAVEYGBP5560
60B PAVEYGBP5661
61B PAVEYGBP5762
62B PAVEYGBP5863
63GBP 1957
Sums
Cell Formulas
RangeFormula
C21,C63,C43,C32C21=LET(s,SUM(FILTER(C$1:D20,A$1:A20=A20)),IF(s<0,s,""))
D21,D63,D43,D32D21=LET(s,SUM(FILTER(C$1:D20,A$1:A20=A20)),IF(s<0,"",s))
 
Upvote 0
Peter_SSs said:
Looks like that last sum is in the wrong column?

Is this what you mean, assuming that you have the LET function?

21 03 22.xlsm
ABCD
1NameCurrencyDebitCredit
2A ISAACGBP1-2
3A ISAACGBP2-3
4A ISAACGBP3-4
5A ISAACGBP4-5
6A ISAACGBP5-6
7A ISAACGBP6-7
8A ISAACGBP7-8
9A ISAACGBP8-9
10A ISAACGBP9-10
11A ISAACGBP10-11
12A ISAACGBP11-12
13A ISAACGBP12-13
14A ISAACGBP13-14
15A ISAACGBP14-15
16A ISAACGBP15-16
17A ISAACGBP16-17
18A ISAACGBP17-18
19A ISAACGBP18-19
20A ISAACGBP19-20
21GBP-19 
22A JACKSONGBP20-1
23A JACKSONGBP21-1
24A JACKSONGBP22-1
25A JACKSONGBP23-1
26A JACKSONGBP24-1
27A JACKSONGBP25-1
28A JACKSONGBP26-1
29A JACKSONGBP27-1
30A JACKSONGBP28-1
31A JACKSONGBP29-1
32GBP 235
33A MELROSEGBP30-35
34A MELROSEGBP31-36
35A MELROSEGBP32-37
36A MELROSEGBP33-38
37A MELROSEGBP34-39
38A MELROSEGBP35-40
39A MELROSEGBP36-41
40A MELROSEGBP37-42
41A MELROSEGBP38-43
42A MELROSEGBP39-44
43GBP-50 
44B PAVEYGBP4045
45B PAVEYGBP4146
46B PAVEYGBP4247
47B PAVEYGBP4348
48B PAVEYGBP4449
49B PAVEYGBP4550
50B PAVEYGBP4651
51B PAVEYGBP4752
52B PAVEYGBP4853
53B PAVEYGBP4954
54B PAVEYGBP5055
55B PAVEYGBP5156
56B PAVEYGBP5257
57B PAVEYGBP5358
58B PAVEYGBP5459
59B PAVEYGBP5560
60B PAVEYGBP5661
61B PAVEYGBP5762
62B PAVEYGBP5863
63GBP 1957
Sums
Cell Formulas
RangeFormula
C21,C63,C43,C32C21=LET(s,SUM(FILTER(C$1:D20,A$1:A20=A20)),IF(s<0,s,""))
D21,D63,D43,D32D21=LET(s,SUM(FILTER(C$1:D20,A$1:A20=A20)),IF(s<0,"",s))
Click to expand...
So I am using this code in my vba

Dim rA As Range

For Each rA In Columns("C:D").SpecialCells(xlConstants, xlNumbers).Areas
rA.Cells(rA.Cells.count + 1).Formula = "=SUM(" & rA.Address & ")"

Where and how could I implement your formula?


Next
 
Upvote 0
Marq1995 said:
I do
Click to expand...
OK, test this

VBA Code: 
Sub Marq1995()
  Dim rA As Range
  
  For Each rA In Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks)
    rA.Offset(, 2).Resize(, 2).FormulaR1C1 = Array( _
      "=LET(s,SUM(FILTER(R1C:R[-1]C[1],R1C1:R[-1]C1=R[-1]C1)),IF(s<0,s,""""))", _
      "=LET(s,SUM(FILTER(R1C[-1]:R[-1]C,R1C1:R[-1]C1=R[-1]C1)),IF(s<0,"""",s))")
  Next rA
End Sub
 
Upvote 0
It looks like this has worked!

Could you please explain the code to me??
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Sum of field in pivot table
Replies
0
Views
139
mabubakerali
M
A
  • Solved
exclude zero and precede it on userform when populate data in listbox
Replies
4
Views
311
abdelfattah
A
R
  • Solved
VBA - Summing a dynamic gap range between two named ranges?
2
Replies
13
Views
339
RockandGrohl
R
J
  • Question
Excel VBA to Sum to bottom of column of data
Replies
3
Views
612
Joe4
Joe4
T
  • Solved
Match, Index problem
Replies
5
Views
343
awoohaw
awoohaw

Forum statistics

Threads
1,214,525
Messages
6,120,052
Members
448,940
Latest member
mdusw

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