If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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' |
#2
|
|||
|
|||
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' |
#3
|
|||
|
|||
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' |
#4
|
|||
|
|||
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' |
#5
|
|||
|
|||
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' |
Thread Tools | |
Display Modes | |
|
|