[SOLVED] Compare row of three ETFs (text) to previous row of three ETFs (text) for changes. Order not counted. Across large dataset in google sheets

Compare row of three ETFs (text) to previous row of three ETFs (text) for changes. Order not counted. Across large dataset in google sheets

I'm trying to track trades (changes) in a portfolio that uses 3 ETFs which are ranked, and so can alter month-to-month. Basically I need a formula that can compare 3 text cells to 3 previous text cells for any changes, and automatically updates as new data is added. The "gotcha" is that the order of the cells doesn't matter (i.e. if the top 2 ETFs switch spots it doesn't matter) since this does not trigger a trade. So it's only when one of the ETFs changes/drops-out that I want to track it.

Here is my dataset, and what I'm trying to get as a result (Change count column):

Month	Top 1	Top 2	Top 3	Change Count	Description
Jan	TIP	IEF	HYG
Feb	IEF	TIP	HYG	0	Same ETFs, just diff order, therefore count = 0
Mar	HYG	TIP	BIL	1	One ETF changed (BIL)
April	TIP	HYG	BIL	0	Same ETFs, just diff order, therefore count = 0
May	DBC	IEF	BIL	2	Two ETFs changes

I have a COUNTIF that works, but can't seem to figure out how to expand this automatically (byrow, or arrayformula).

My current formula:

=3-(COUNTIF(B2:D2,""&B3&"")+COUNTIF(B2:D2,""&C3&"")+COUNTIF(B2:D2,""&D3&""))

Appreciate any and all help!

Solution

You may try:

=byrow(B3:D,lambda(Σ,if(counta(Σ)=0,,counta(ifna(filter(Σ,isna(xmatch(Σ,offset(Σ,-1,)))))))))