How to combine column data with same category (similar to tags) together

[Tuckebarry]

Hey everyone, I'm creating a trading journal to analyze my performance of different strategies. However, there are times where a trade may have multiple different strategies in play. For example, 'Earnings Play' + 'Oversold' + 'Breakout'. I'd like to create graphs to compare the performance of these strategies, but how could I make Excel consider them together. Right now I listed the headers as 'Strategy 1', 'Strategy 2', 'Strategy 3' although this doesn't combine it.

^That's how my spreadsheet looks like right now, but it won't combine the data together.

^^That's how it should look when all strategies are combined^^

Thank you so much in advance for your help!

 

[Legacy 143009]

If I were you I would insert two helper columns. Let say the first helper column address is R. R4 formula will be:

=INDIRECT(TEXT(MIN(IF(($H$4:$L$25<>"")*(COUNTIF($R3$1:R3,$H$4:$L$25)=0),ROW($4:$25)*100+COLUMN($H:$L),7^8)),"R0C00"),)&""

Drag it down as many as strategies you have.
A few things about this formula:
It uses INDIRECT function which is not recommended. It is a volatile function that will cause worksheet to recalculate whenever you open or change the file. I am sure there is a more efficient way to achieve this in Oficce 365.
Let say your second helper column is S. S4 formula will be:

=SUMPRODUCT(--(((H$4:H$25=R4)+(I$4:I$25=R4)+(J$4:J$25=R4)+(K$4:K$25=R4))>0),(amount_column)*(quantity_column))

Then you can draw the desired graph by using these two columns.

 

[Tuckebarry]

If I were you I would insert two helper columns. Let say the first helper column address is R. R4 formula will be:

=INDIRECT(TEXT(MIN(IF(($H$4:$L$25<>"")*(COUNTIF($R3$1:R3,$H$4:$L$25)=0),ROW($4:$25)*100+COLUMN($H:$L),7^8)),"R0C00"),)&""

Drag it down as many as strategies you have.
A few things about this formula:
It uses INDIRECT function which is not recommended. It is a volatile function that will cause worksheet to recalculate whenever you open or change the file. I am sure there is a more efficient way to achieve this in Oficce 365.
Let say your second helper column is S. S4 formula will be:

=SUMPRODUCT(--(((H$4:H$25=R4)+(I$4:I$25=R4)+(J$4:J$25=R4)+(K$4:K$25=R4))>0),(amount_column)*(quantity_column))

Then you can draw the desired graph by using these two columns.

Thanks a lot for taking the time to answer! Honestly at this point I think I'm going to just create a separate sheet with all the strategies and calculate the stats individually.

I really appreciate it though, thank you so much!

 

[Legacy 143009]

Yes, that would be a much simplier solution than what I suggested. Thaks for the response

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to combine column data with same category (similar to tags) together
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.