# sum by searching up based on a different column

• 04-04-2019, 01:31 AM
melvinkoshy
sum by searching up based on a different column
I want sum based on key t in total column. The logic is as shown in the figure

The Total column should sum the data by searching up the t in key column

How do we achieve this in the Total field?
• 04-04-2019, 02:41 AM
Pepe Le Mokko
Re: sum by searching up based on a different column
In D3 =IF(B3="t",SUM(\$C\$3:C3),"")
In D4 =IF(B4="t",SUM(\$C\$3:C4)-SUM(\$D\$3:D3),"") and pull down as needed
• 04-05-2019, 12:03 AM
melvinkoshy
Re: sum by searching up based on a different column
As I preferred a structured formula for the table, I modified the key flag to a number instead of t and used the formula in Cell D3
IF(B4<>"", SUM(C3:INDEX(C:C, MATCH(1E+99, B\$1:B3))), "")

However, I am not getting the total when there are multiple cells to be added. Total should appear corresponding to the cell where 1 is present in the key. Please help tweak the code?
• 04-05-2019, 02:49 AM
Pepe Le Mokko
Re: sum by searching up based on a different column
Whats is wrong with the provided answer?
• 04-12-2019, 10:01 PM
melvinkoshy
Re: sum by searching up based on a different column
i desire the total to be against the same row as the "key"
• 04-12-2019, 10:55 PM
bebo021999
Re: sum by searching up based on a different column
Try in D3:

Code:

`=IF(B3=1,SUM(\$C\$2:C3)-SUM(\$D\$2:D2),"")`