How ro Apply Indirect to 3 formulas-> Countif, Indirect to Min/If/Row(array) and sumproduct?

[AndyJR]

Hi and happy holidays!!

I know Indirect is volatile but I need it to add because such formulas that i'm using are referenced to a data range that is replaced with row insertion on daily basis (Insert/row with new values on the same range) via VBA.
So, when Excel calculate the new values, The 3 formulas pull the previous values (not the new one).

I really need to apply the Indirect Function to the following Formulas :

Formula 1

=COUNTIF($E$37:$I$43,E3)

Formula2 (array)

{=MIN(IF($E$37:$I$337=E3,ROW($C$37:$C$337)-ROW($C$37)+0))}

Formula 3

=SUMPRODUCT(--($E$37:$I$351&$Q$37:$U$351=E3&J3))

Please, can I get some help with this?

Note: as you see on this example the 3 formulas are using the same reference.

Thank You in advance!

 

[Aladin Akyurek]

[1]
=countif(indirect("e37:i43"),e3)

[2]
{=min(if(indirect("e37:i337")=e3,row(indirect("e37:i337"))-row(indirect("e37"))+1))}

[3]
=sumproduct(--(indirect("e37:i351")&indirect("q37:u351"))=e3&j3))

 

[AndyJR]

Thank you, Aladin Akyurek

The Formulas Number 1 and 2 Work Perfect, but the Sumproduct display 0 (zero) in every cells.

Don't worry, I'm sure your formula is correct, I think my sheet has some range with text and numbers.
Let you know.


Thank you so much,!!!!

 

[Aladin Akyurek]

Thank you, Aladin Akyurek

The Formulas Number 1 and 2 Work Perfect, but the Sumproduct display 0 (zero) in every cells.

Don't worry, I'm sure your formula is correct, I think my sheet has some range with text and numbers.
Let you know.


Thank you so much,!!!!

Let's modify the SUMPRODUCT formula as follows:

=SUMPRODUCT(--(INDIRECT("e37:i351")=E3),--(INDIRECT("q37:u351")=J3))