sum positives/negatives by type

Gross GL by type.xlsx
Hi,

I am looking to get "gross" figures. See attachment. I have one column with positive and negative numbers. I have another column with "types". I would like a formula that will calculate the "gross" gains or losses by type (i.e. Apples, Oranges or Bananas.)

Hoping for a formula that doesn't require macros or coding.

Thanks!

Thanks so much for the welcome. First post/question! =D

And to answer your question and clarify: I probably should have excluded column J, but it was trying to keep the example as close to my actual (can't share the actual data). But I would use the same formula used for column I in column J. I used fruit, but imagine it's "Apples" are apple stock on the NYSE, I bought over a year ago and some I bought a month ago, and for this example, for the exact same price, and then sell them all at once. I need to determine what my short term gains are and short term losses are (for my tax return). Then I need to determine what my long term gains and LT losses are. Thus the column I and J. I is for short term and J is for long term. So the numbers are the gains/losses based upon "holding period" which I use Column H to calculate in the example.

Hope that clears up the confusion. Thanks again!

Originally Posted by BB1972

Moshin, welcome to the forum.



Do you mean you have two columns - I13:I20, which are positive numbers, and J13:J20, which you have custom formatted, but are in fact negative numbers? Or am I looking in the wrong place?

In the example in the highlighted section is where I would put the formula, and currently have the "correct" answers. Thanks again!

Oh no! But yes, your formula is exactly what I was looking for. Oddly, I had tried a formula very similar, but I think it didn't work as I was using entire columns ("A:A" versus A1:A30) and I was getting zeroes. But when I tried switching your formula to entire columns, it told me it created a circular reference (not sure why I didn't get that before). No explanation needed.... BUT THANK YOU SO MUCH!

Originally Posted by BB1972

Actually, at first, it just resulted in a lot of head-scratching! HOWEVER,I think I understand what you want now. Can you have a look at the attached, and let me know if that's what you had mind? I'm happy to explain the formula if required, but I'll save my fingers until such time as you confirm this is what you want.

Hope this helps.