using a cell from another sheet as reference

Question

I wrote a formula which calculates the inputs in "Sheet2" which is below. So J4 is a cell of Sheet2:

=((COUNTIF((INDIRECT(ADDRESS(ROW(J4);COLUMN(J4))&":J"& 
(MIN(IF(A4:A107="";ROW(A4:A107))))));" 
 <>"&""))-1)/((COUNTIF((INDIRECT(ADDRESS(ROW(J4);COLUMN(J4))&":J"& 
(MIN(IF(A4:A107="";ROW(A4:A107))))));"<>0"))-1)

Now I want to write this formula to a cell in Sheet1.It means i should reference the Sheet2 for calculating the formula.

Can anyone help me with referencing?

EDIT: I still am without solution for this issue. Can anyone suggest me something new then in the comments?

Answer 1

Looking at your formula, it seems you want to evaluate ratio of:

Count non-blank cells in column J where column A is also non-blank. Divide this count by count of non-zero cells in column J where column A is non-blank.

If yes then test following formula and see if it works per your needs:

=COUNTIFS(Sheet2!A4:A107,"<>",Sheet2!J4:J107,"<>")/COUNTIFS(Sheet2!A4:A107,"<>",Sheet2!J4:J107,"<>0")

Make sure you change argument separators.

Edit

In that case, you need to use following array formula (CTRL+SHIFT+ENTER) and change argument separators.

=COUNTIFS(Sheet2!A4:INDEX(Sheet2!A4:A107,MIN(IF(Sheet2!A4:A107="",ROW(Sheet2!A4:A107)))),"<>",Sheet2!J4:INDEX(Sheet2!J4:J107,MIN(IF(Sheet2!A4:A107="",ROW(Sheet2!A4:A107)))),"<>")/COUNTIFS(Sheet2!A4:INDEX(Sheet2!A4:A107,MIN(IF(Sheet2!A4:A107="",ROW(Sheet2!A4:A107)))),"<>",Sheet2!J4:INDEX(Sheet2!J4:J107,MIN(IF(Sheet2!A4:A107="",ROW(Sheet2!A4:A107)))),"<>0")