How would you find a replace this?

[RockandGrohl]

=SUMIFS('Booking Data'!$I:$I, 'Booking Data'!$D:$D, $S$6, 'Booking Data'!$S:$S, $U7, 'Booking Data'!$T:$T, W$4, 'Booking Data'!$J:$J, "<>X",'Booking Data'!$U:$U,'JG EU'!$C$2)+SUMIFS('Booking Data'!$V:$V, 'Booking Data'!$D:$D, $S$6, 'Booking Data'!$S:$S, $U7, 'Booking Data'!$T:$T, W$4, 'Booking Data'!$J:$J, "<>X",'Booking Data'!$U:$U,'JG EU'!$C$2)


=SUMIFS('Booking Data'!$I:$I, 'Booking Data'!$D:$D, $S$6, 'Booking Data'!$S:$S, $U7, 'Booking Data'!$T:$T, W$4, 'Booking Data'!$J:$J, "<>X",'Booking Data'!$U:$U,'JG EU'!$C$2)+SUMIFS('Booking Data'!$V:$V, 'Booking Data'!$D:$D, $S$6, 'Booking Data'!$S:$S, $U7, 'Booking Data'!$T:$T, W$4, 'Booking Data'!$J:$J, "X",'Booking Data'!$U:$U,'JG EU'!$C$2)


I need those "Does not equal" signs gone from the second SUMIFS, as they are in the bottom string.

However, I can't find and replace as there are locked cell references.

I have a sheet with multiple tabs which total thousands of instances of this formula, multiplied by 4 different sheets.


How would you remove all the <> from just the second SUMIFS? Thanks.

 

[jkpieterse]

Download my Flexfind tool from https://jkp-ads.com/officemarketplaceff-en.asp

 

[RockandGrohl]

Download my Flexfind tool from https://jkp-ads.com/officemarketplaceff-en.asp

I'm not seeing how this can solve my problem - there's no option in Flexfind to replace text but ignore cell references, for example. Thanks.

 

[jkpieterse]

Have it search for the text "<>X" (without the quotes), it should find the formulas containing that and group formulas which you can copy into one single entry. You can then choose the ones you want to replace and enter the repacement string. The tool will prompt for each occurrence in each formula separately.

 

[RockandGrohl]

There's probably 2,000 instances of this occurring (the original formula was set up by an ex-member of the team and he copied it to multiple matrixes and then duplicated the matrixes across multiple sheets on multiple workbooks, I've been tasked with swapping it.

I couldn't find a way to do and it and it ended up being easier just correcting one, copying it down then across, for each of the matrixes it appears in. An arduous, boring and time-consuming job but it had to be done, shame there isn't a faster way. I appreciate your solution but it's essentially requiring me to sit there and click "Next / Apply / Next / Apply" a thousand + times.

 

[RoryA]

I suspect that if you switched to R1C1 referencing, you could have done a replace on the entire second part of the formula.