Sum values between recurring text

[Bahal]

Hi,

Just looking to see if there is a method to sum values in a range between recurring words in a separate column.

I am trying to use one column for the formula in the total column. So far "=IFERROR(IF($B4*$C4=0,0,$B4*$C4),0)" is in column D with some conditional formatting. I would like to sum together the values for each transfer in the same row as "Transfer Note :" in column D.

Thank you in advance for any assistance.

	A	B	C	D
1	Name	Quantity	Cost	Total
2	Transfer Date :	24/01/2019 13:45:15
3	Transfer Note :	Freddy
4	Pencil	10	2.50	25.00
5	Soda	3	4.00	12.00
6	Transfer Date :	24/01/2019 13:43:21
7	Transfer Note :	Sherise
8	Chips	6	5.30	31.80
9	Transfer Date :	23/01/2019 10:15:54
10	Transfer Note :	Terry
11	Soda	2	4.00	8.00
12	Lollies	8	8.20	16.40
13	Transfer Date :	23/01/2019 09:58:30
14	Transfer Note :	Steve
15	Paper	13	0.17	2.21
16	Beanie	2	12.50	25.00
17	Milk	5	3.75	18.75
18

<tbody>
</tbody>

 

[alansidman]

I took your data and normalized so that I could create a pivot table. I imported your data into Power Query and ran the following MCode.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Quantity", type any}, {"Cost", type number}, {"Total", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Transfer Date", each if[Name]="Transfer Date :" then [Quantity] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Transfer Note", each if [Name]= "Transfer Note :" then [Quantity] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Transfer Date", "Transfer Note"}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Filled Down", {"Cost"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Cost] <> null)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Transfer Date", "Transfer Note", "Name", "Quantity", "Cost", "Total"})
in
    #"Reordered Columns"

With the data back in Excel Native, I then created a Pivot Table as shown below.

Data Range

	H​	I​	J​	K​
1​	​	​	​	​
2​	Transfer Date​	Transfer Note​	Name​	Sum of Total​
3​	23/01/2019 09:58:30​	​	​	​
4​	​	Steve​	​	​
5​	​	​	Beanie​	25​
6​	​	​	Milk​	18.75​
7​	​	​	Paper​	2.21​
8​	​	Steve Total​	​	45.96​
9​	23/01/2019 09:58:30 Total​	​	​	45.96​
10​	23/01/2019 10:15:54​	​	​	​
11​	​	Terry​	​	​
12​	​	​	Lollies​	16.4​
13​	​	​	Soda​	8​
14​	​	Terry Total​	​	24.4​
15​	23/01/2019 10:15:54 Total​	​	​	24.4​
16​	24/01/2019 13:43:21​	​	​	​
17​	​	Sherise​	​	​
18​	​	​	Chips​	31.8​
19​	​	Sherise Total​	​	31.8​
20​	24/01/2019 13:43:21 Total​	​	​	31.8​
21​	24/01/2019 13:45:15​	​	​	​
22​	​	Freddy​	​	​
23​	​	​	Pencil​	25​
24​	​	​	Soda​	12​
25​	​	Freddy Total​	​	37​
26​	24/01/2019 13:45:15 Total​	​	​	37​

 

[Bahal]

Thanks for the reply.

I'm not very good with pivot tables and in my table before, columns A to C are a generated report from a third party program and I was hoping to have column D be copy and pasted into the generated report thus totalling each item and then each transfer note in the same column. I wasn't sure if that was even possible and couldn't find anything through Googling.

 

[sandy666]

is that what you want?

Name	Quantity	Cost	Total	Sum
Transfer Date :	24/01/2019 13:45:15
Transfer Note :	Freddy			37​
Pencil	10	2.5​	25​
Soda	3	4​	12​
Transfer Date :	24/01/2019 13:43:21
Transfer Note :	Sherise			31.8​
Chips	6	5.3​	31.8​
Transfer Date :	23/01/2019 10:15:54
Transfer Note :	Terry			24.4​
Soda	2	4​	8​
Lollies	8	8.2​	16.4​
Transfer Date :	23/01/2019 09:58:30
Transfer Note :	Steve			45.96​
Paper	13	0.17​	2.21​
Beanie	2	12.5​	25​
Milk	5	3.75​	18.75​

or

Name	Quantity	Cost	Totals
Transfer Date :	24/01/2019 13:45:15
Transfer Note :	Freddy		37​
Pencil	10	2.5​	25​
Soda	3	4​	12​
Transfer Date :	24/01/2019 13:43:21
Transfer Note :	Sherise		31.8​
Chips	6	5.3​	31.8​
Transfer Date :	23/01/2019 10:15:54
Transfer Note :	Terry		24.4​
Soda	2	4​	8​
Lollies	8	8.2​	16.4​
Transfer Date :	23/01/2019 09:58:30
Transfer Note :	Steve		45.96​
Paper	13	0.17​	2.21​
Beanie	2	12.5​	25​
Milk	5	3.75​	18.75​

or post expected result

 

[alansidman]

Since your raw data is not normalized, you need to normalize it for analysis and computation. And Pivot Tables are important to analysis. Suggest you look at some on line tutorials to get this important feature as part of your excel repertoire. Otherwise, I have nothing more to offer on this issue. Good luck with finding an alternative solution.

 

[Bahal]

is that what you want?

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Quantity[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Cost[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Total[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Sum[/COLOR]
Transfer Date :	24/01/2019 13:45:15
Transfer Note :	Freddy			37​
Pencil	10	2.5​	25​
Soda	3	4​	12​
Transfer Date :	24/01/2019 13:43:21
Transfer Note :	Sherise			31.8​
Chips	6	5.3​	31.8​
Transfer Date :	23/01/2019 10:15:54
Transfer Note :	Terry			24.4​
Soda	2	4​	8​
Lollies	8	8.2​	16.4​
Transfer Date :	23/01/2019 09:58:30
Transfer Note :	Steve			45.96​
Paper	13	0.17​	2.21​
Beanie	2	12.5​	25​
Milk	5	3.75​	18.75​

<tbody>
</tbody>

or

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Quantity[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Cost[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Totals[/COLOR]
Transfer Date :	24/01/2019 13:45:15
Transfer Note :	Freddy		37​
Pencil	10	2.5​	25​
Soda	3	4​	12​
Transfer Date :	24/01/2019 13:43:21
Transfer Note :	Sherise		31.8​
Chips	6	5.3​	31.8​
Transfer Date :	23/01/2019 10:15:54
Transfer Note :	Terry		24.4​
Soda	2	4​	8​
Lollies	8	8.2​	16.4​
Transfer Date :	23/01/2019 09:58:30
Transfer Note :	Steve		45.96​
Paper	13	0.17​	2.21​
Beanie	2	12.5​	25​
Milk	5	3.75​	18.75​

<tbody>
</tbody>

or post expected result

The second table is what I as after.

 

[Bahal]

Thanks for your help, I'll look for some videos on pivot tables.

 

[Eric W]

Try this formula in D2, and copy down:

=IF(A2="Transfer Note :",SUM(D3:INDEX(D3:D23,IFERROR(MATCH("Transfer Date :",A3:A23,0),20))),IFERROR(B2*C2,""))

This assumes a maximum of 20 rows per section. If you anticipate more than that, change the values in red.

 

[sandy666]

The second table is what I as after.

here is a PowerQuery solution: excel file