|
Can Someone tell me why this won't work?????????????
=COUNTIF('BNSF 2176:BNSF 8037'!I1,"GP38")
I am attempting to count the number of times the text string "GP38" is present in cell "I1" of a select group of worksheets within the same work book. The error I get is "#value" When I run error checking on the funtion, it says that a value in the formula is of the wrong data type. I was under the impression that "COUNTIF" could be used for text also. Thanks, Justin |
Can Someone tell me why this won't work?????????????
It does, but not 3d.
Try this. Put the names of all the target sheets in M1:Mn and use =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:Mn&"'!I1"),"GP 38")) obviously adjust to n to suit. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... =COUNTIF('BNSF 2176:BNSF 8037'!I1,"GP38") I am attempting to count the number of times the text string "GP38" is present in cell "I1" of a select group of worksheets within the same work book. The error I get is "#value" When I run error checking on the funtion, it says that a value in the formula is of the wrong data type. I was under the impression that "COUNTIF" could be used for text also. Thanks, Justin |
Can Someone tell me why this won't work?????????????
does any thing work in 3d?
"Bob Phillips" wrote: It does, but not 3d. Try this. Put the names of all the target sheets in M1:Mn and use =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:Mn&"'!I1"),"GP 38")) obviously adjust to n to suit. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... =COUNTIF('BNSF 2176:BNSF 8037'!I1,"GP38") I am attempting to count the number of times the text string "GP38" is present in cell "I1" of a select group of worksheets within the same work book. The error I get is "#value" When I run error checking on the funtion, it says that a value in the formula is of the wrong data type. I was under the impression that "COUNTIF" could be used for text also. Thanks, Justin |
Can Someone tell me why this won't work?????????????
On Tue, 19 Jun 2007 04:07:03 -0700, skijsh1979
wrote: does any thing work in 3d? Usually the quickest way to answer these kinds of questions is to look at HELP for your particular product. For example, in Excel 2003: Functions that can be used in a 3-D reference SUM - adds numbers AVERAGE - calculates average (arithmetic mean) of numbers AVERAGEA - calculates average (arithmetic mean) of numbers; includes text and logicals COUNT - counts cells that contain numbers COUNTA - counts cells that are not empty MAX - finds largest value in a set of values MAXA - finds largest value in a set of values; includes text and logicals MIN - finds smallest value in a set of values MINA - finds smallest value in a set of values; includes text and logicals PRODUCT - multiplies numbers STDEV - calculates standard deviation based on a sample STDEVA - calculates standard deviation based on a sample; includes text and logicals STDEVP - calculates standard deviation of an entire population STDEVPA - calculates standard deviation of an entire population; includes text and logicals VAR - estimates variance based on a sample VARA - estimates variance based on a sample; includes text and logicals VARP - calculates variance for an entire population VARPA - calculates variance for an entire population; includes text and logicals --ron |
Can Someone tell me why this won't work?????????????
From that list, I don't see anything that will count text strings in 3-d. So
I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 04:07:03 -0700, skijsh1979 wrote: does any thing work in 3d? Usually the quickest way to answer these kinds of questions is to look at HELP for your particular product. For example, in Excel 2003: Functions that can be used in a 3-D reference SUM - adds numbers AVERAGE - calculates average (arithmetic mean) of numbers AVERAGEA - calculates average (arithmetic mean) of numbers; includes text and logicals COUNT - counts cells that contain numbers COUNTA - counts cells that are not empty MAX - finds largest value in a set of values MAXA - finds largest value in a set of values; includes text and logicals MIN - finds smallest value in a set of values MINA - finds smallest value in a set of values; includes text and logicals PRODUCT - multiplies numbers STDEV - calculates standard deviation based on a sample STDEVA - calculates standard deviation based on a sample; includes text and logicals STDEVP - calculates standard deviation of an entire population STDEVPA - calculates standard deviation of an entire population; includes text and logicals VAR - estimates variance based on a sample VARA - estimates variance based on a sample; includes text and logicals VARP - calculates variance for an entire population VARPA - calculates variance for an entire population; includes text and logicals --ron |
Can Someone tell me why this won't work?????????????
You already got a solution by Bob Phillips, that is the way you do a the
equivalent of a countif over multiple sheets -- Regards, Peo Sjoblom "skijsh1979" wrote in message ... From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 04:07:03 -0700, skijsh1979 wrote: does any thing work in 3d? Usually the quickest way to answer these kinds of questions is to look at HELP for your particular product. For example, in Excel 2003: Functions that can be used in a 3-D reference SUM - adds numbers AVERAGE - calculates average (arithmetic mean) of numbers AVERAGEA - calculates average (arithmetic mean) of numbers; includes text and logicals COUNT - counts cells that contain numbers COUNTA - counts cells that are not empty MAX - finds largest value in a set of values MAXA - finds largest value in a set of values; includes text and logicals MIN - finds smallest value in a set of values MINA - finds smallest value in a set of values; includes text and logicals PRODUCT - multiplies numbers STDEV - calculates standard deviation based on a sample STDEVA - calculates standard deviation based on a sample; includes text and logicals STDEVP - calculates standard deviation of an entire population STDEVPA - calculates standard deviation of an entire population; includes text and logicals VAR - estimates variance based on a sample VARA - estimates variance based on a sample; includes text and logicals VARP - calculates variance for an entire population VARPA - calculates variance for an entire population; includes text and logicals --ron |
Can Someone tell me why this won't work?????????????
On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979
wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
It is not working, the result is REF#.
The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
send me your workbook, I will look at it for you
note my signature re my email address -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... It is not working, the result is REF#. The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
Bob,
You should have 2 copies, the first can be trashed. The second has an explanation of what I am trying to do at this point. I think I did alot of it the hard way, so if there is anything you see that can be done more efficiently, let me know. Thanks Justin "Bob Phillips" wrote: send me your workbook, I will look at it for you note my signature re my email address -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... It is not working, the result is REF#. The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
Nothing has arrived here.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, You should have 2 copies, the first can be trashed. The second has an explanation of what I am trying to do at this point. I think I did alot of it the hard way, so if there is anything you see that can be done more efficiently, let me know. Thanks Justin "Bob Phillips" wrote: send me your workbook, I will look at it for you note my signature re my email address -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... It is not working, the result is REF#. The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
I sent it again. I somewhat past the previous problem. I am now trying to
count text strings based on more than one criteria. Example: A B C D E F G 1 a b c a f d a 2 z y v z y v z 3 y z y x z y y 4 z z z v y y x 5 x v x z v z y 6 z y y 7 y z v I want to count the number of times "y" shows up a a column that has the string "a" in row 1 of the particular column or columns. Is there a way to count just that data? Justin "Bob Phillips" wrote: Nothing has arrived here. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, You should have 2 copies, the first can be trashed. The second has an explanation of what I am trying to do at this point. I think I did alot of it the hard way, so if there is anything you see that can be done more efficiently, let me know. Thanks Justin "Bob Phillips" wrote: send me your workbook, I will look at it for you note my signature re my email address -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... It is not working, the result is REF#. The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
=SUMPRODUCT((A2:G7="y")*(A1:G1="a"))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... I sent it again. I somewhat past the previous problem. I am now trying to count text strings based on more than one criteria. Example: A B C D E F G 1 a b c a f d a 2 z y v z y v z 3 y z y x z y y 4 z z z v y y x 5 x v x z v z y 6 z y y 7 y z v I want to count the number of times "y" shows up a a column that has the string "a" in row 1 of the particular column or columns. Is there a way to count just that data? Justin "Bob Phillips" wrote: Nothing has arrived here. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, You should have 2 copies, the first can be trashed. The second has an explanation of what I am trying to do at this point. I think I did alot of it the hard way, so if there is anything you see that can be done more efficiently, let me know. Thanks Justin "Bob Phillips" wrote: send me your workbook, I will look at it for you note my signature re my email address -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... It is not working, the result is REF#. The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
Bob,
That worked great. Did you ever get the email of the spread sheet? "Bob Phillips" wrote: =SUMPRODUCT((A2:G7="y")*(A1:G1="a")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... I sent it again. I somewhat past the previous problem. I am now trying to count text strings based on more than one criteria. Example: A B C D E F G 1 a b c a f d a 2 z y v z y v z 3 y z y x z y y 4 z z z v y y x 5 x v x z v z y 6 z y y 7 y z v I want to count the number of times "y" shows up a a column that has the string "a" in row 1 of the particular column or columns. Is there a way to count just that data? Justin "Bob Phillips" wrote: Nothing has arrived here. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, You should have 2 copies, the first can be trashed. The second has an explanation of what I am trying to do at this point. I think I did alot of it the hard way, so if there is anything you see that can be done more efficiently, let me know. Thanks Justin "Bob Phillips" wrote: send me your workbook, I will look at it for you note my signature re my email address -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... It is not working, the result is REF#. The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
Bob,
is there a way to identify the different text strings in a certain area and then list them all on a seperate sheet? Justin "Bob Phillips" wrote: =SUMPRODUCT((A2:G7="y")*(A1:G1="a")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... I sent it again. I somewhat past the previous problem. I am now trying to count text strings based on more than one criteria. Example: A B C D E F G 1 a b c a f d a 2 z y v z y v z 3 y z y x z y y 4 z z z v y y x 5 x v x z v z y 6 z y y 7 y z v I want to count the number of times "y" shows up a a column that has the string "a" in row 1 of the particular column or columns. Is there a way to count just that data? Justin "Bob Phillips" wrote: Nothing has arrived here. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, You should have 2 copies, the first can be trashed. The second has an explanation of what I am trying to do at this point. I think I did alot of it the hard way, so if there is anything you see that can be done more efficiently, let me know. Thanks Justin "Bob Phillips" wrote: send me your workbook, I will look at it for you note my signature re my email address -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... It is not working, the result is REF#. The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
No, I never did.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, That worked great. Did you ever get the email of the spread sheet? "Bob Phillips" wrote: =SUMPRODUCT((A2:G7="y")*(A1:G1="a")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... I sent it again. I somewhat past the previous problem. I am now trying to count text strings based on more than one criteria. Example: A B C D E F G 1 a b c a f d a 2 z y v z y v z 3 y z y x z y y 4 z z z v y y x 5 x v x z v z y 6 z y y 7 y z v I want to count the number of times "y" shows up a a column that has the string "a" in row 1 of the particular column or columns. Is there a way to count just that data? Justin "Bob Phillips" wrote: Nothing has arrived here. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, You should have 2 copies, the first can be trashed. The second has an explanation of what I am trying to do at this point. I think I did alot of it the hard way, so if there is anything you see that can be done more efficiently, let me know. Thanks Justin "Bob Phillips" wrote: send me your workbook, I will look at it for you note my signature re my email address -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... It is not working, the result is REF#. The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
Which strings, the SP gets told what they are?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, is there a way to identify the different text strings in a certain area and then list them all on a seperate sheet? Justin "Bob Phillips" wrote: =SUMPRODUCT((A2:G7="y")*(A1:G1="a")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... I sent it again. I somewhat past the previous problem. I am now trying to count text strings based on more than one criteria. Example: A B C D E F G 1 a b c a f d a 2 z y v z y v z 3 y z y x z y y 4 z z z v y y x 5 x v x z v z y 6 z y y 7 y z v I want to count the number of times "y" shows up a a column that has the string "a" in row 1 of the particular column or columns. Is there a way to count just that data? Justin "Bob Phillips" wrote: Nothing has arrived here. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, You should have 2 copies, the first can be trashed. The second has an explanation of what I am trying to do at this point. I think I did alot of it the hard way, so if there is anything you see that can be done more efficiently, let me know. Thanks Justin "Bob Phillips" wrote: send me your workbook, I will look at it for you note my signature re my email address -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... It is not working, the result is REF#. The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
I have M2:AS32 that have a lists of locomotive defects. I want to create
another list that has only 1 of each of the defects that are present in that group of cells. I don't know how many there will be. "Bob Phillips" wrote: Which strings, the SP gets told what they are? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, is there a way to identify the different text strings in a certain area and then list them all on a seperate sheet? Justin "Bob Phillips" wrote: =SUMPRODUCT((A2:G7="y")*(A1:G1="a")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... I sent it again. I somewhat past the previous problem. I am now trying to count text strings based on more than one criteria. Example: A B C D E F G 1 a b c a f d a 2 z y v z y v z 3 y z y x z y y 4 z z z v y y x 5 x v x z v z y 6 z y y 7 y z v I want to count the number of times "y" shows up a a column that has the string "a" in row 1 of the particular column or columns. Is there a way to count just that data? Justin "Bob Phillips" wrote: Nothing has arrived here. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, You should have 2 copies, the first can be trashed. The second has an explanation of what I am trying to do at this point. I think I did alot of it the hard way, so if there is anything you see that can be done more efficiently, let me know. Thanks Justin "Bob Phillips" wrote: send me your workbook, I will look at it for you note my signature re my email address -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... It is not working, the result is REF#. The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
Can Someone tell me why this won't work?????????????
The dataset is the same as the example I made. Only catch is that each
defect type can only be listed 1 time in the new list. i am going to be building frequency ratios based on this data. "Bob Phillips" wrote: Which strings, the SP gets told what they are? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, is there a way to identify the different text strings in a certain area and then list them all on a seperate sheet? Justin "Bob Phillips" wrote: =SUMPRODUCT((A2:G7="y")*(A1:G1="a")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... I sent it again. I somewhat past the previous problem. I am now trying to count text strings based on more than one criteria. Example: A B C D E F G 1 a b c a f d a 2 z y v z y v z 3 y z y x z y y 4 z z z v y y x 5 x v x z v z y 6 z y y 7 y z v I want to count the number of times "y" shows up a a column that has the string "a" in row 1 of the particular column or columns. Is there a way to count just that data? Justin "Bob Phillips" wrote: Nothing has arrived here. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... Bob, You should have 2 copies, the first can be trashed. The second has an explanation of what I am trying to do at this point. I think I did alot of it the hard way, so if there is anything you see that can be done more efficiently, let me know. Thanks Justin "Bob Phillips" wrote: send me your workbook, I will look at it for you note my signature re my email address -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "skijsh1979" wrote in message ... It is not working, the result is REF#. The formula that I have entered is: =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M33&"'!I1"),"G P38")) I have listed the titles of all the sheets that need need to be checked for the occurance of "GP38 in cell I1 of each respective sheet. I must be doing something wrong. "Ron Rosenfeld" wrote: On Tue, 19 Jun 2007 15:28:21 -0700, skijsh1979 wrote: From that list, I don't see anything that will count text strings in 3-d. So I am guessing that I will have to make a cell on each page that corrosponds with the particular text string that I am attempting to count? Is there any other way to accomplish this. I have around 40 sheets and 12 seperate text strings that I am going to be counting. Did you try Bob Phillips suggestion using INDIRECT? If not, you could try the COUNTIF.3D function from Longre's free morefunc.xll add-in available at http://xcell05.free.fr/. This should work if you have XL2003 or earlier, and if the range does not have more than 65536 cells. I do not know if it will work in XL2007. --ron |
| All times are GMT +1. The time now is 12:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com