Sum values between recurring text

B

Bahal

New Member
Joined
Jan 23, 2019
Messages
4
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.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Quantity[/TD]
[TD]Cost[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Transfer Date :[/TD]
[TD]24/01/2019 13:45:15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Transfer Note :[/TD]
[TD]Freddy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Pencil[/TD]
[TD]10[/TD]
[TD]2.50[/TD]
[TD]25.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Soda[/TD]
[TD]3[/TD]
[TD]4.00[/TD]
[TD]12.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Transfer Date :[/TD]
[TD]24/01/2019 13:43:21[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Transfer Note :[/TD]
[TD]Sherise[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Chips[/TD]
[TD]6[/TD]
[TD]5.30[/TD]
[TD]31.80[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Transfer Date :[/TD]
[TD]23/01/2019 10:15:54[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Transfer Note :[/TD]
[TD]Terry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Soda[/TD]
[TD]2[/TD]
[TD]4.00[/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Lollies[/TD]
[TD]8[/TD]
[TD]8.20[/TD]
[TD]16.40[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Transfer Date :[/TD]
[TD]23/01/2019 09:58:30[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Transfer Note :[/TD]
[TD]Steve[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Paper[/TD]
[TD]13[/TD]
[TD]0.17[/TD]
[TD]2.21[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Beanie[/TD]
[TD]2[/TD]
[TD]12.50[/TD]
[TD]25.00[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Milk[/TD]
[TD]5[/TD]
[TD]3.75[/TD]
[TD]18.75[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
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.

Code: 
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
[Table="class: grid"][tr][td] [/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][td]
K​
[/td][/tr]
[tr][td]
1​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
2​
[/td][td]
Transfer Date​
[/td][td]
Transfer Note​
[/td][td]
Name​
[/td][td]
Sum of Total​
[/td][/tr]


[tr][td]
3​
[/td][td]
23/01/2019 09:58:30​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
4​
[/td][td]
​
[/td][td]
Steve​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
5​
[/td][td]
​
[/td][td]
​
[/td][td]
Beanie​
[/td][td]
25​
[/td][/tr]


[tr][td]
6​
[/td][td]
​
[/td][td]
​
[/td][td]
Milk​
[/td][td]
18.75​
[/td][/tr]


[tr][td]
7​
[/td][td]
​
[/td][td]
​
[/td][td]
Paper​
[/td][td]
2.21​
[/td][/tr]


[tr][td]
8​
[/td][td]
​
[/td][td]
Steve Total​
[/td][td]
​
[/td][td]
45.96​
[/td][/tr]


[tr][td]
9​
[/td][td]
23/01/2019 09:58:30 Total​
[/td][td]
​
[/td][td]
​
[/td][td]
45.96​
[/td][/tr]


[tr][td]
10​
[/td][td]
23/01/2019 10:15:54​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
11​
[/td][td]
​
[/td][td]
Terry​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
12​
[/td][td]
​
[/td][td]
​
[/td][td]
Lollies​
[/td][td]
16.4​
[/td][/tr]


[tr][td]
13​
[/td][td]
​
[/td][td]
​
[/td][td]
Soda​
[/td][td]
8​
[/td][/tr]


[tr][td]
14​
[/td][td]
​
[/td][td]
Terry Total​
[/td][td]
​
[/td][td]
24.4​
[/td][/tr]


[tr][td]
15​
[/td][td]
23/01/2019 10:15:54 Total​
[/td][td]
​
[/td][td]
​
[/td][td]
24.4​
[/td][/tr]


[tr][td]
16​
[/td][td]
24/01/2019 13:43:21​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
17​
[/td][td]
​
[/td][td]
Sherise​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
18​
[/td][td]
​
[/td][td]
​
[/td][td]
Chips​
[/td][td]
31.8​
[/td][/tr]


[tr][td]
19​
[/td][td]
​
[/td][td]
Sherise Total​
[/td][td]
​
[/td][td]
31.8​
[/td][/tr]


[tr][td]
20​
[/td][td]
24/01/2019 13:43:21 Total​
[/td][td]
​
[/td][td]
​
[/td][td]
31.8​
[/td][/tr]


[tr][td]
21​
[/td][td]
24/01/2019 13:45:15​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
22​
[/td][td]
​
[/td][td]
Freddy​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr][td]
23​
[/td][td]
​
[/td][td]
​
[/td][td]
Pencil​
[/td][td]
25​
[/td][/tr]


[tr][td]
24​
[/td][td]
​
[/td][td]
​
[/td][td]
Soda​
[/td][td]
12​
[/td][/tr]


[tr][td]
25​
[/td][td]
​
[/td][td]
Freddy Total​
[/td][td]
​
[/td][td]
37​
[/td][/tr]


[tr][td]
26​
[/td][td]
24/01/2019 13:45:15 Total​
[/td][td]
​
[/td][td]
​
[/td][td]
37​
[/td][/tr]
[/table]
 
Upvote 0
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.
 
Upvote 0
is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Quantity[/td][td=bgcolor:#70AD47]Cost[/td][td=bgcolor:#70AD47]Total[/td][td=bgcolor:#70AD47]Sum[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Transfer Date :[/td][td=bgcolor:#E2EFDA]24/01/2019 13:45:15[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Transfer Note :[/td][td]Freddy[/td][td][/td][td][/td][td]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pencil[/td][td=bgcolor:#E2EFDA]10[/td][td=bgcolor:#E2EFDA]
2.5​
[/td][td=bgcolor:#E2EFDA]
25​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Soda[/td][td]3[/td][td]
4​
[/td][td]
12​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Transfer Date :[/td][td=bgcolor:#E2EFDA]24/01/2019 13:43:21[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Transfer Note :[/td][td]Sherise[/td][td][/td][td][/td][td]
31.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Chips[/td][td=bgcolor:#E2EFDA]6[/td][td=bgcolor:#E2EFDA]
5.3​
[/td][td=bgcolor:#E2EFDA]
31.8​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Transfer Date :[/td][td]23/01/2019 10:15:54[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Transfer Note :[/td][td=bgcolor:#E2EFDA]Terry[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
24.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Soda[/td][td]2[/td][td]
4​
[/td][td]
8​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Lollies[/td][td=bgcolor:#E2EFDA]8[/td][td=bgcolor:#E2EFDA]
8.2​
[/td][td=bgcolor:#E2EFDA]
16.4​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Transfer Date :[/td][td]23/01/2019 09:58:30[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Transfer Note :[/td][td=bgcolor:#E2EFDA]Steve[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
45.96​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Paper[/td][td]13[/td][td]
0.17​
[/td][td]
2.21​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Beanie[/td][td=bgcolor:#E2EFDA]2[/td][td=bgcolor:#E2EFDA]
12.5​
[/td][td=bgcolor:#E2EFDA]
25​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Milk[/td][td]5[/td][td]
3.75​
[/td][td]
18.75​
[/td][td][/td][/tr]
[/table]

or

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Quantity[/td][td=bgcolor:#70AD47]Cost[/td][td=bgcolor:#70AD47]Totals[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Transfer Date :[/td][td=bgcolor:#E2EFDA]24/01/2019 13:45:15[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Transfer Note :[/td][td]Freddy[/td][td][/td][td]
37​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pencil[/td][td=bgcolor:#E2EFDA]10[/td][td=bgcolor:#E2EFDA]
2.5​
[/td][td=bgcolor:#E2EFDA]
25​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Soda[/td][td]3[/td][td]
4​
[/td][td]
12​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Transfer Date :[/td][td=bgcolor:#E2EFDA]24/01/2019 13:43:21[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Transfer Note :[/td][td]Sherise[/td][td][/td][td]
31.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Chips[/td][td=bgcolor:#E2EFDA]6[/td][td=bgcolor:#E2EFDA]
5.3​
[/td][td=bgcolor:#E2EFDA]
31.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Transfer Date :[/td][td]23/01/2019 10:15:54[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Transfer Note :[/td][td=bgcolor:#E2EFDA]Terry[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
24.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Soda[/td][td]2[/td][td]
4​
[/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Lollies[/td][td=bgcolor:#E2EFDA]8[/td][td=bgcolor:#E2EFDA]
8.2​
[/td][td=bgcolor:#E2EFDA]
16.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Transfer Date :[/td][td]23/01/2019 09:58:30[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Transfer Note :[/td][td=bgcolor:#E2EFDA]Steve[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
45.96​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Paper[/td][td]13[/td][td]
0.17​
[/td][td]
2.21​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Beanie[/td][td=bgcolor:#E2EFDA]2[/td][td=bgcolor:#E2EFDA]
12.5​
[/td][td=bgcolor:#E2EFDA]
25​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Milk[/td][td]5[/td][td]
3.75​
[/td][td]
18.75​
[/td][/tr]
[/table]


or post expected result
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
sandy666 said:
is that what you want?

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


or

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



or post expected result
Click to expand...

The second table is what I as after.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

ExcelNewbie2020
  • Solved
Extract unique values and sum duplicates
Replies
3
Views
241
alansidman
alansidman
O
  • Question Question
sum or subtract for the same cell based on match column between two sheets
2
Replies
13
Views
844
Omran Y
O
S
  • Solved
Sum column based on unique values in another column
2
Replies
11
Views
1K
samide2001
S
M
  • Question Question
Displaying number based on date conditions column and True/False column.
Replies
2
Views
281
mike4777
M
C
  • Question Question
Sum Data Across Columns and Rows between Dates
2
Replies
19
Views
714
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

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