define range (C1:C24) with formula

Hi,

I want to do countifs formula and for range of counting I would like to define with formula. e.g. instead of C1:C24 I would like to make formula that would return 24 (or different number depending on position). I tried it with match formula, but it doesnt want to work the way I entered it. Any idea what is wrong?

COUNTIFS('GENO-MED (genofit)'!$C$1:$C(match("meja";'Genome Solutions Sdn Bhd'!C:C;0));">1";'GENO-MED (genofit)'!$D$1:$D$(match("meja";'Genome Solutions Sdn Bhd'!D:D;0));"<>")

thanks

Re: define range (C1:C24) with formula

Looks like interesting formula, but I am having problems putting it into context. I am not sure that I understand how it is used:

e.g. I tried to use this as part of formula - to define range, but even this is not working
INDIRECT("C1:C"&MATCH("meja";C:C;0);FALSE)

i was hoping to get range C1:C21 (number 21 in this case), and than I was thinking of putting this in formula in my first post. But clearly there are mistakes. Any suggestions on what I missed?

Re: define range (C1:C24) with formula

Hi,
I got swamped with other work and was not able to check it sooner. Even though your were quick with your help. Sorry for that.

I checked it now, and this is working on the same sheet:
COUNTIFS(INDIRECT("C1:C"&MATCH("meja";C1:C10000;0));">1")

I also tried it with data from other sheet and it works.
COUNTIFS(INDIRECT("'Sheet1'!$C$1:$C"&MATCH("meja";'Sheet1'!$C$1:$C10000;0));">1")

Now I wanted to link the name of sheet to value in cell, since I have many repeats of this formula. But this I could not get to work. e.g.:
COUNTIFS(INDIRECT("'A1336'!$C$1:$C"&MATCH("meja";'A1336'!$C$1:$C10000;0));">1")

Maybe any ideas on how this could be solved?

Re: define range (C1:C24) with formula

The first part is working, but the second one (green) is not working. It doesn't look like it is linking to cell.
=COUNTIFS(INDIRECT("'"&A1336&"'!$C$1:$C"&MATCH("meja";'A1336'!$C$1:$C10000;0));">1")

I also tried it like in the first part (not sure what the logic behind "&A1336&" is or why it is working in this way), but it also didn't work.
=COUNTIFS(INDIRECT("'"&A1336&"'!$C$1:$C"&MATCH("meja";'"&A1336&"'!$C$1:$C10000;0));">1")

Re: define range (C1:C24) with formula

Here is sample of my workbook.
forum.ozgrid.com/index.php?attachment/71969/

Re: define range (C1:C24) with formula

It works. Thanks.

Can I ask why I need to put cell number in brackets and & symbol on both sides? I mean, why doesn't it work without? So that I can understand it better for next use

Re: define range (C1:C24) with formula

It is little more clear now, but I would need to check indirect formula explanation again. Thanks though.

So you suggest it can be substituted for concatenate function? Just putting text together something like this? (of course the one below is not working - why would it on the dry run :P)
=countifs(CONCATENATE(';A1336;'!;$C;1;":";$C77");">1")