I have a drop-down in J3. The selections are Overall, A, B, C, D, E
If I choose Overall, I want to search for a date (N3) from all the companies in my Named Range "Companies". The problem is, this formula is not working properly.
If I use an array, it sort of works, but when I apply to other cells and change the final reference at the end to match, from V4 to say, V10, I get #VALUE error.
=SUMPRODUCT(COUNTIFS(IF($J$3="Overall",INDIRECT("'"&Companies&"'!A:A"),INDIRECT("'"&$J$3&"'!A:A")),"<"&$G$42,IF($J$3="Overall",INDIRECT("'"&Companies&"'!A:A"),INDIRECT("'"&$J$3&"'!A:A")),">="&$N$3,IF($J$3="Overall",INDIRECT("'"&Companies&"'!D:D"),INDIRECT("'"&$J$3&"'!D:D")),$V4))