# 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),"")`