Hi all,

Been racking my brains putting this formula together. I have a Countif, which uses an indirect function R1C1 style, to count across a range (the Indirect function allows this to be dynamic). See below, this is working perfectly:

=COUNTIF(INDIRECT("'Output Data'!"&"R1"&"C"&(MATCH(A5,'Output Data'!$A$2:$BBC$2,0))&":"&"R10000"&"C"&(MATCH(A5,'Output Data'!$A$2:$BBC$2,0)),FALSE),1)

However, I have a few further criteria to add (using a Countifs function). Example below - which is giving me a #VALUE error, I am unsure why? The only difference is the red appended part of the formula and the fact that it is a COUNTIFS, rather than a COUNTIF.

=COUNTIFS(INDIRECT("'sheet 1'!"&"R1"&"C"&(MATCH(A6,'sheet 1'!$A$2:$BBC$2,0))&":"&"R10000"&"C"&(MATCH(A6,'sheet 1'!$A$2:$BBC$2,0)),FALSE),1,'sheet 1'!$C:$C,'sheet 2'!F$2)

Any help/explanation would be helpful!

Thanks!