countif using other cell values for 'range' and 'criteria'
 

using excel 2007
I am trying to set up a countif function that can easily be used by basic
excel users.

what i want to do is have a couple of cells where the users type the text
they want to search for and in which column, the countif formula would be in
a seperate cell and would use the user entered values as the 'range' and
'criteria' values. Basically a countif function that doesnt require the users
to manipulate the formula for each new search.

the data is held on sheet 1 (approx 1000 rows), the user will enter values
on sheet 2.
eg user types A:A and postgrad

Then countif formula picks up these values and counts on sheet 1, col A for
the entry 'postgrad'

countif using other cell values for 'range' and 'criteria'
 

Try

=COUNTIF(INDIRECT(A1),A2)

---
HTH

Bob Phillips

"tony" wrote in message
...
using excel 2007
I am trying to set up a countif function that can easily be used by basic
excel users.

what i want to do is have a couple of cells where the users type the text
they want to search for and in which column, the countif formula would be
in
a seperate cell and would use the user entered values as the 'range' and
'criteria' values. Basically a countif function that doesnt require the
users
to manipulate the formula for each new search.

the data is held on sheet 1 (approx 1000 rows), the user will enter values
on sheet 2.
eg user types A:A and postgrad

Then countif formula picks up these values and counts on sheet 1, col A
for
the entry 'postgrad'

countif using other cell values for 'range' and 'criteria'
 

Use INDIRECT():

If A1 thru A6 contain:

qwerty
qwerty
qwerty
qwerty
asdf
asdf

and D1 contains A1:A6
and E1 contains qwerty

then

=COUNTIF(INDIRECT(D1),E1)


will display 4
--
Gary''s Student - gsnu200909


"tony" wrote:

using excel 2007
I am trying to set up a countif function that can easily be used by basic
excel users.

what i want to do is have a couple of cells where the users type the text
they want to search for and in which column, the countif formula would be in
a seperate cell and would use the user entered values as the 'range' and
'criteria' values. Basically a countif function that doesnt require the users
to manipulate the formula for each new search.

the data is held on sheet 1 (approx 1000 rows), the user will enter values
on sheet 2.
eg user types A:A and postgrad

Then countif formula picks up these values and counts on sheet 1, col A for
the entry 'postgrad'

countif using other cell values for 'range' and 'criteria'
 

Thanks Gary and Bob, inital tests show this is the function i need!

A further question: I want to simplify this as much as possible for the end
users, and as the range I am looking at is on another sheet, can i include
the 'sheet 1' ref within the INDIRECT function (or elswehere within COUNTIF),
or does the user have to enter this when they type (for example) A:A?

i.e. using Garys example below, if A1:A6 are on sheet 1 and the COUNTIF is
on sheet 2


cheers,
Tony

"Gary''s Student" wrote:

Use INDIRECT():

If A1 thru A6 contain:

qwerty
qwerty
qwerty
qwerty
asdf
asdf

and D1 contains A1:A6
and E1 contains qwerty

then

=COUNTIF(INDIRECT(D1),E1)


will display 4
--
Gary''s Student - gsnu200909


"tony" wrote:

using excel 2007
I am trying to set up a countif function that can easily be used by basic
excel users.

what i want to do is have a couple of cells where the users type the text
they want to search for and in which column, the countif formula would be in
a seperate cell and would use the user entered values as the 'range' and
'criteria' values. Basically a countif function that doesnt require the users
to manipulate the formula for each new search.

the data is held on sheet 1 (approx 1000 rows), the user will enter values
on sheet 2.
eg user types A:A and postgrad

Then countif formula picks up these values and counts on sheet 1, col A for
the entry 'postgrad'

countif using other cell values for 'range' and 'criteria'
 

D1 would then just contain

Sheet1!A1:A6

just as with other functions

HTH

Bob

"tony" wrote in message
...
Thanks Gary and Bob, inital tests show this is the function i need!

A further question: I want to simplify this as much as possible for the
end
users, and as the range I am looking at is on another sheet, can i include
the 'sheet 1' ref within the INDIRECT function (or elswehere within
COUNTIF),
or does the user have to enter this when they type (for example) A:A?

i.e. using Garys example below, if A1:A6 are on sheet 1 and the COUNTIF is
on sheet 2


cheers,
Tony

"Gary''s Student" wrote:

Use INDIRECT():

If A1 thru A6 contain:

qwerty
qwerty
qwerty
qwerty
asdf
asdf

and D1 contains A1:A6
and E1 contains qwerty

then

=COUNTIF(INDIRECT(D1),E1)


will display 4
--
Gary''s Student - gsnu200909


"tony" wrote:

using excel 2007
I am trying to set up a countif function that can easily be used by
basic
excel users.

what i want to do is have a couple of cells where the users type the
text
they want to search for and in which column, the countif formula would
be in
a seperate cell and would use the user entered values as the 'range'
and
'criteria' values. Basically a countif function that doesnt require the
users
to manipulate the formula for each new search.

the data is held on sheet 1 (approx 1000 rows), the user will enter
values
on sheet 2.
eg user types A:A and postgrad

Then countif formula picks up these values and counts on sheet 1, col A
for
the entry 'postgrad'