# 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

Hello,

To gain flexibility ... you will need the Indirect() function ... :wink:

For an excellent explanation ... see Debra's site http://www.contextures.com/xlFunctions05.html

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• 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

Hello,

You could test INDIRECT("C1:C"&MATCH("meja";C1:C50;0))

HTH

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: define range (C1:C24) with formula

Hello,

Is No News ... equivalent to ... Good News ...???

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• 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

Maybe this?

=COUNTIFS(INDIRECT("'"&A1336&"'!\$C\$1:\$C"&MATCH("meja";'A1336'!\$C\$1:\$C10000;0));">1")

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: define range (C1:C24) with formula

Try these:

[COLOR="#0000FF"]=COUNTIFS(INDIRECT("'"&A3&"'!\$C\$1:\$C"&MATCH("meja",INDIRECT("'"& A3 & "'!\$C\$1:\$C10000"),0)),">1")[/COLOR]

and

[COLOR="#0000FF"]=COUNTIFS(INDIRECT("'"&A3&"'!\$C\$1:\$C"&MATCH("meja",INDIRECT("'"& A3 & "'!\$C\$1:\$C10000"),0)),">1",INDIRECT("'"&A3&"'!\$d\$1:\$d"&MATCH("meja",INDIRECT("'"& A3 & "'!\$d\$1:\$d10000"),0)),"<>")[/COLOR]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• 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

With the INDIRECT function you are basically constructing a text string by concatenating different objects together... then referencing the string as a whole to "indirectly" reference the address you constructed.

So to create a link to a separate sheet e.g. 'Data Sheet'!A1 where the name of the sheet, "Data Sheet" is in a cell, say A1.. .then you will need to construct the reference... so you need the initial apostrophe first, so you enclose that in quotes like "'", then since your sheet name is in a cell, you need to reference that cell so you concatenate the reference with the & symbols (note, we could also use the CONCATENATE function)., then you add the closing apostrophe, exclamation point and the columnar reference on the other sheet in quotes... "'!C1:C10000". The INDIRECT function puts it all together.

Try using the formula evaluation tools found in the Formulas tab to see how the formula gets evaluated step by step

Where there is a will there are many ways. Finding one that works for you is the challenge!

• 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")

• Re: define range (C1:C24) with formula

The CONCATENATE section should look more like this:

CONCATENATE("'";A1336;"'!\$C1\$C77")

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!