summing values using specific conditions

Hello I am new to this forum so please bear with me...

I am looking for help in developing steps in a spreadsheet to calculate a sum based on a few conditions. The basis of my spreadsheet works fine, however I am trying to add the contents of a cell to another by checking other cells.

For example:

Cells A1:A10 contain a numerical sequence (1-10 respectively) to indicate a "Node"

Cells B1:B10 contain a user selected number (1-10) which defines the Node it is fed from. We'll call this "Fed From Node".......ie. If Cell B2=1, that means that Node 2 is fed from Node 1.

Cells C1:C10 contain a value which is input by the user. We'll call this "Amps"

Cells D1:D10 would show the total "Amps" of the current cell added to all others that are fed from same.

What I am ultimately trying to figure out is how to add the "Amps" to each "Node" and show the results in column D.

More specifically if the spreadshet looks like the following:

Node-----Fed From Node-----Amps-------Total
1--------------0----------------------0--------------22.5
2--------------1---------------------10--------------22.5
3--------------0----------------------0---------------0
4--------------2---------------------7.5-------------12.5
5--------------4----------------------5---------------5

How would I add the "Amps" from Node5 to Node4 and Node4 to Node2 to achieve a total of 22.5 IF Node5 is fed from Node4 and Node4 is fed from Node2?

I have tried macros and different excel formulas for the past few days and I am really stumped.

Any help would be greatly appreciated.

Welcome to the Board.

Could you perhaps explain why Node 1 has a total of 22.5 given fed from Node 0, yet Node 3 which is also fed from Node 0 has a total of 0.

Having just now re-read your post, if we assume your headers are in A1:D1 and your sample values in A2:C6 then I can replicate your results (Total values) in D2:D6 using:

D2: =$C2+LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX($D3:$D$7,MATCH($A2,$B3:$B$7,0),1)))
copied down to D6

The above assumes a 1:1 relationship in terms of "From Node"... ie no Node is used to "supply" more than one other Node... (this can be changed if necessary).

However, I'm still not convinced the values you put for Total correspond the results you actually want to obtain... so please post up with more info if the above is not what you're after.

Given 1:many the key is to then change from an INDEX/MATCH (singular return) to a SUMIF (multiple) ... eg:

D3:
=$C3+LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,SUMIF($B4:$B$22,$A3,$D4:$D$22)))
copied down

In d2 and copy down to D21,

=$C2 + SUMIF($B3:$B$21, $A2, D3:D$21)

shg, thanks for pointing that out ... er... it's Sunday (that's my excuse!)

I think it's a little unusual formula as Excel goes, in that it requires a downward reference to build the result; sort of recursive.

Notes to elecengr:

1. From the top of the list, nodes must appear in column A before appearing in column B.

2. Leave a blank row below the data.

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes