Total Column at text break in next column

W

whitfieldcraig

New Member
Joined
Dec 13, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi.

I have the following table of information and I am looking to create a formula that totals in the next column on the last entry/break when column A entry changes with the added complexity if column C changes even if column A does not. The total in column E preferably just needs to be a formula covering the whole column returning a blank if the criteria isn't met.

Thanks.

POR001988XYZWH1$1,815.00
POR001988XYZWH1$1,815.00
POR001988XYZWH1$1,815.00
POR001988XYZWH1$1,815.00
POR001988XYZWH1$1,815.00
POR001988XYZWH1$470.00
POR001988XYZWH1$8,875.00
POR001988XYZWH1$11,280.00
POR001988XYZWH1$15,000.00
POR001988XYZWH1$36,250.00
POR001988XYZWH1$9,210.00
POR001988XYZWH1$6,000.00
POR001988XYZWH1$2,250.00
POR001988XYZWH1$3,380.00$101,790.00
POR001988XYZWH2$4,290.00
POR001988XYZWH2$235.00
POR001988XYZWH2$542.00
POR001988XYZWH2$705.00
POR001988XYZWH2$9,400.00
POR001988XYZWH2$12,000.00
POR001988XYZWH2$14,500.00$41,672.00
POR001989XYZWH1$2,300.00$2,300.00
POR001990XYZWH1$3,435.00
POR001990XYZWH1$3,144.00
POR001990XYZWH1$1,902.00
POR001990XYZWH1$3,608.00
POR001990XYZWH1$3,336.00
POR001990XYZWH1$3,840.00
POR001990XYZWH1$3,512.00
POR001990XYZWH1$3,180.00$25,957.00
POR001990XYZWH2$2,096.00
POR001990XYZWH2$795.00$2,891.00
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
In E1 try:
Excel Formula: 
=IF(OR(C1<>C2,A1<>A2),SUMIFS(D$1:D1,C$1:C1,C1,A$1:A1,A1),"")

And copy down

Book1
ABCDE
1POR001988XYZWH11 815,00 zł 
2POR001988XYZWH11 815,00 zł 
3POR001988XYZWH11 815,00 zł 
4POR001988XYZWH11 815,00 zł 
5POR001988XYZWH11 815,00 zł 
6POR001988XYZWH1470,00 zł 
7POR001988XYZWH18 875,00 zł 
8POR001988XYZWH111 280,00 zł 
9POR001988XYZWH115 000,00 zł 
10POR001988XYZWH136 250,00 zł 
11POR001988XYZWH19 210,00 zł 
12POR001988XYZWH16 000,00 zł 
13POR001988XYZWH12 250,00 zł 
14POR001988XYZWH13 380,00 zł101 790,00 zł
15POR001988XYZWH24 290,00 zł 
16POR001988XYZWH2235,00 zł 
17POR001988XYZWH2542,00 zł 
18POR001988XYZWH2705,00 zł 
19POR001988XYZWH29 400,00 zł 
20POR001988XYZWH212 000,00 zł 
21POR001988XYZWH214 500,00 zł41 672,00 zł
22POR001989XYZWH12 300,00 zł2 300,00 zł
23POR001990XYZWH13 435,00 zł 
24POR001990XYZWH13 144,00 zł 
25POR001990XYZWH11 902,00 zł 
26POR001990XYZWH13 608,00 zł 
27POR001990XYZWH13 336,00 zł 
28POR001990XYZWH13 840,00 zł 
29POR001990XYZWH13 512,00 zł 
30POR001990XYZWH13 180,00 zł25 957,00 zł
31POR001990XYZWH22 096,00 zł 
32POR001990XYZWH2795,00 zł2 891,00 zł
Sheet1
Cell Formulas
RangeFormula
E1:E32E1=IF(OR(C1<>C2,A1<>A2),SUMIFS(D$1:D1,C$1:C1,C1,A$1:A1,A1),"")
 
Upvote 0
Another way, assuming a header row

24 12 14.xlsm
ABCDE
1Header1Header2Header3Header4Header5
2POR001988XYZWH1$1,815.00 
3POR001988XYZWH1$1,815.00 
4POR001988XYZWH1$1,815.00 
5POR001988XYZWH1$1,815.00 
6POR001988XYZWH1$1,815.00 
7POR001988XYZWH1$470.00 
8POR001988XYZWH1$8,875.00 
9POR001988XYZWH1$11,280.00 
10POR001988XYZWH1$15,000.00 
11POR001988XYZWH1$36,250.00 
12POR001988XYZWH1$9,210.00 
13POR001988XYZWH1$6,000.00 
14POR001988XYZWH1$2,250.00 
15POR001988XYZWH1$3,380.00$101,790.00
16POR001988XYZWH2$4,290.00 
17POR001988XYZWH2$235.00 
18POR001988XYZWH2$542.00 
19POR001988XYZWH2$705.00 
20POR001988XYZWH2$9,400.00 
21POR001988XYZWH2$12,000.00 
22POR001988XYZWH2$14,500.00$41,672.00
23POR001989XYZWH1$2,300.00$2,300.00
24POR001990XYZWH1$3,435.00 
25POR001990XYZWH1$3,144.00 
26POR001990XYZWH1$1,902.00 
27POR001990XYZWH1$3,608.00 
28POR001990XYZWH1$3,336.00 
29POR001990XYZWH1$3,840.00 
30POR001990XYZWH1$3,512.00 
31POR001990XYZWH1$3,180.00$25,957.00
32POR001990XYZWH2$2,096.00 
33POR001990XYZWH2$795.00$2,891.00
Sum groups
Cell Formulas
RangeFormula
E2:E33E2=IF(OR(C2<>C3,A2<>A3),SUM(D$2:D2)-SUM(E$1:E1),"")
 
Upvote 0
Kaper said:
In E1 try:
Excel Formula: 
=IF(OR(C1<>C2,A1<>A2),SUMIFS(D$1:D1,C$1:C1,C1,A$1:A1,A1),"")

And copy down

Book1
ABCDE
1POR001988XYZWH11 815,00 zł 
2POR001988XYZWH11 815,00 zł 
3POR001988XYZWH11 815,00 zł 
4POR001988XYZWH11 815,00 zł 
5POR001988XYZWH11 815,00 zł 
6POR001988XYZWH1470,00 zł 
7POR001988XYZWH18 875,00 zł 
8POR001988XYZWH111 280,00 zł 
9POR001988XYZWH115 000,00 zł 
10POR001988XYZWH136 250,00 zł 
11POR001988XYZWH19 210,00 zł 
12POR001988XYZWH16 000,00 zł 
13POR001988XYZWH12 250,00 zł 
14POR001988XYZWH13 380,00 zł101 790,00 zł
15POR001988XYZWH24 290,00 zł 
16POR001988XYZWH2235,00 zł 
17POR001988XYZWH2542,00 zł 
18POR001988XYZWH2705,00 zł 
19POR001988XYZWH29 400,00 zł 
20POR001988XYZWH212 000,00 zł 
21POR001988XYZWH214 500,00 zł41 672,00 zł
22POR001989XYZWH12 300,00 zł2 300,00 zł
23POR001990XYZWH13 435,00 zł 
24POR001990XYZWH13 144,00 zł 
25POR001990XYZWH11 902,00 zł 
26POR001990XYZWH13 608,00 zł 
27POR001990XYZWH13 336,00 zł 
28POR001990XYZWH13 840,00 zł 
29POR001990XYZWH13 512,00 zł 
30POR001990XYZWH13 180,00 zł25 957,00 zł
31POR001990XYZWH22 096,00 zł 
32POR001990XYZWH2795,00 zł2 891,00 zł
Sheet1
Cell Formulas
RangeFormula
E1:E32E1=IF(OR(C1<>C2,A1<>A2),SUMIFS(D$1:D1,C$1:C1,C1,A$1:A1,A1),"")
Click to expand...
Excellent. Thanks for that.
 
Upvote 0
You're welcome. Glad you got a solution that suits you. Thanks for the follow-up. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Best formula for data lookup from Store#, Dept #, and Date from a lot of data
Replies
1
Views
746
KLund1
K

Forum statistics

Threads
1,224,847
Messages
6,181,330
Members
453,032
Latest member
Pauh

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