Edit formula not to consider blank cells with formulas

[aswethink]

Hi there,

How can I edit the following array formula not to consider blank cells that contain formulas?

D3 =INDEX(A$3:A$20,AGGREGATE(15,6,ROW(A$3:A$20)/(INDEX($E$3:$J$6,N(IF(1,MATCH($B$3:$B$20,$D$3:$D$6,))),N(IF(1,COLUMN($E$3:$J$6)-COLUMN($D$3))))>0),ROWS(L$3:L3))-ROW(A$2))

Currently, the formula works in a range where blank cells that are truly empty, such as E3:J6 in the attached mock file. However, I get the wrong results when I use it in the actual file where blank cells in a range have formulas. I tried replacing >0 with >"" but nothing happened.

This should be fairly easy but I can't quite get it right.

Appreciate the help!
Cheers

[aswethink]

To be specific, what is happening is that in the mockup file, MO-2 is displayed only 4 times, representing the 4 non-empty cells in E4:J4. However, in the actual file, MO-2 is displayed 6 times, and I think this is happening because the blank cells in the actual file, represented by E4:J4 in the mockup, have formulas in them.

[Glenn Kennedy]

Please post a sample file that SHOWS the problem instead of one that doesn't. BtW, it is NOT an array formula (a thing of the past with O365).

[aswethink]

Thanks for the reminder, Glenn. I'll reconstruct the problem in the mockup and send it thereafter.

[aswethink]

Sheet1 displays the formula working in D3.
Sheet2 displays the problem. The formula works but is also including blank cells in its assessment of U3:Z7.

U3:Z7 in Sheet1 do not have formulas.
U3:Z7 in Sheet2 contain formulas.

I would like to edit the formula in D3 of Sheet2 to display the same results as those found in column L:L of Sheet1.

[Glenn Kennedy]

D2, copied across and down:

=IFERROR(INDEX(A$3:A$20,AGGREGATE(15,6,ROW(A$3:A$20)/(INDEX($U$3:$Z$7,N(IF(1,MATCH($B$3:$B$20,$T$3:$T$7,))),N(IF(1,COLUMN($U$3:$Z$7)-COLUMN($T$3))))<>""),ROWS(D$3:D3))-ROW(A$2)),"")

F2, copied down:

=INDEX($U$3:$Z$7,MATCH(E3,$T$3:$T$7,0),AGGREGATE(15,6,(COLUMN($T:$Z)-COLUMN($T:$T)+1)/(INDEX($U$3:$Z$7,MATCH($D3&$E3,$D3&$T$3:$T$7,0),)<>""),COUNTIFS($D$3:$D3,$D3,$E$3:$E3,$E3)))

[aswethink]

Oh so that's why it would not work. I did attempt to replace >0 with <>"" in D3, but thought F3 would just follow and did not need to be changed.

I also realised I have been referencing the wrong cells since the beginning of my post. I'll go edit it them.

Thank you very much, Glenn!

[Glenn Kennedy]

You're welcome & thanks for the feedback.

[aswethink]

You're welcome!