helper cell that uses concatenate

[johnny52]

I have formulas over many rows. I think it might be slowing down the worksheet calculations.
I have an example of one of my formulas, but I don't know what I would put in the helper cell to speed things up. I never even heard of helper cells!
If possible, what would it look like? Thanks everybody
If this is not a good formula, maybe someone could put in another example for me to apply

=IF(COUNT(1/BVV2),SUMPRODUCT(--(C$2:C$900=C2),--ISNUMBER(BVV$2:BVV$900),--(BVV$2:BVV$900>0),--(BVV$2:BVV$900>BVV2))+1,"")

 

[DanteAmor]

Try the following:
Let's put a help column, let's say column BVX, in cell BVX2 put the following formula and copy it down.

=AND(ISNUMBER(BVV2),BVV2>0)

Now, in the column where you have your original formula, you put the following formula:

=IF(COUNT(1/BVV2),COUNTIFS(C:C,C2,BVX:BVX,TRUE,BVV:BVV,">"&BVV2)+1,"")

As you can see, we are using the help column (BVX) and also with the COUNTIFS formula, since it is a standard Excel formula, you can use the entire column and you should not have problems in the performance of the formula, that is, the sheet will not be slow .

But if you want to improve it further, you can set the range:

=IF(COUNT(1/BVV2),COUNTIFS($C$2:$C$900,C2,$BVX$2:$BVX$900,TRUE,$BVV$2:$BVV$900,">"&BVV2)+1,"")

Example:

Dante Amor
	BVU	BVV	BVW	BVX	BVY	BVZ
1				Helper Column
2		1		TRUE		2
3		2		TRUE		1
4		4		TRUE		1
5		A		FALSE
6		3		TRUE		1
7		-4		FALSE		3
DANTE AMOR

Cell Formulas
Range	Formula
BVX2:BVX7	BVX2	=AND(ISNUMBER(BVV2),BVV2>0)
BVZ2:BVZ7	BVZ2	=IF(COUNT(1/BVV2),COUNTIFS(C:C,C2,BVX:BVX,TRUE,BVV:BVV,">"&BVV2)+1,"")

Check if the performance of the calculations improved in your sheet.