Compute Results Based on Two Variables

[RussG2]

I want to Sum positive results from cells in a column in another cell, and Sum negative amounts from cells in the same column in another cell.

For example, if the value in Column B of a given row is DJI, and the value in Column E of that row is positive, then include the value from column E in the Sum of all other rows that meet the same criteria in Cell E15. (I will Sum negative numbers in another cell.

I’m asking for the proper syntax for a formula that would look like this:
If B2 = DJI and E2 >0 then add E2 to the sum in E15.

I hope I’m being clear. I’ll try to attach a screen shot.

Thanks!

[erAck]

=SUMIFS(E$2:E$11;B$2:B$11;"DJI";E$2:E$11;">0")

or input DJI as cell content in for example B15 and use

=SUMIFS(E$2:E$11;B$2:B$11;B15;E$2:E$11;">0")

and then pull or copy&paste that formula cell two rows down (hence the $ absolute row references but not for B15) for the NDX and SPX results and input NDX in B16 and SPX in B17.

[RussG2]

Thanks very much for the suggestion! It seems to work.

Being a novice with spreadsheets the use of Absolute References was new to me and I had to step back and research them. I still don’t understand them well, but let me ask this: Since it is likely I will be adding and deleting rows above what is now Row 12, is there any reason I can’t or shouldn’t use Relative References instead of Absolute? What advantage is using Absolute References here?

[erAck]

Absolute references stay anchored when pulling down or copy-pasting a formula cell, relative references are adjusted to point to the same relative offset. If you used the formula with B15 as given above then you’ll notice that B15 becomes B16 when the formula cell is copied to row 16, but the absolute row references don’t change. See also online help.

From this example, adding rows above row 12 (i.e. inserting between row 11 and row 12 and thus after the range reference’s end) would not adjust the formula, but adding above row 11 would. This is independent of whether absolute or relative references are used. Or you’d have to activate Expand references when new columns/rows are inserted under Tools → Options → Calc → General, to have references expand when rows are inserted at the immediate outer edge of a referenced range.