Results 1 to 12 of 12
Thread: Cell Formulas (Office 2000)

20020523, 14:01 #1
 Join Date
 May 2002
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
Cell Formulas (Office 2000)
Hello. I am trying to create a COUNT formula that will look at a criteria in two different columns and then count the number of occurences. Both criteria have to be met for a result of 1 to be returned. I have gotten the formula to work for TRUE/FALSE in which the result is either a 1 or 0. But I need the formula to count the number of occurrences. Can you help? Thanks.

20020523, 14:08 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cell Formulas (Office 2000)
Check out THIS THREAD.
Legare Coleman

20020523, 18:09 #3
 Join Date
 May 2002
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cell Formulas (Office 2000)
Thanks for your quick reply. I am using a wildcard in the formula (*). I have plugged in the formula and I am getting a result of 0. there are two rows that meet the criteria. Is there something I am doing wrong with the wildcard.
Thanks!

20020523, 19:59 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cell Formulas (Office 2000)
It's really tough to tell without seeing what you are doing, could you show us the formula. However, in general, wildcards will not work in the type of formula I pointed you to.
Legare Coleman

20020523, 20:09 #5
 Join Date
 May 2002
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cell Formulas (Office 2000)
Legare,
Thanks for your help. Please look at the enclosed spreadsheet. What I want to do is to have the function look for the date string in Column A in the AllOpen Tab. The date string must use a wildcard so the format will be "200108*". Then it will go to column B and look for a keyword such as "Test*" I must also utililize a wildcard here. The formula will count the number of occurences that these two criteria are meet. For this criteria, there are two that meet the criteria and '2' will be returned to cell I3 in the Type spreadsheet. Thanks for your help. I hope this helps.

20020523, 20:39 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cell Formulas (Office 2000)
The following formula, array entered (hold down Ctrl+Shift when you press enter) looks like it does what you asked:
<pre>=SUM((LEFT(' AllOpen '!A3:A34,6)="200108")*NOT(ISERROR(SEARCH("Test",' AllOpen '!B3:B34))))
</pre>
BTW, it is not really a good idea to put all of those leading and trailing spaces in the sheet names. It can cause you all manner of problems.
I have attached your workbook with the working formula in I3.Legare Coleman

20020524, 16:06 #7
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cell Formulas (Office 2000)
Also
=SUMPRODUCT(ISNUMBER(SEARCH("200108",A3:A34))*ISNU MBER(SEARCH("Test",B3:B34)))
will produce the desire count.Microsoft MVP  Excel

20020607, 13:39 #8
 Join Date
 May 2002
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cell Formulas (Office 2000)
Legare,
Thanks for all of your help. Now I need to get a string which pulls a result by utilizing 3 columns. I tried just adding another string but it did not work. Can you help. Thanks.
nstapleton

20020607, 13:51 #9
 Join Date
 May 2002
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cell Formulas (Office 2000)
Legare,
Thanks for all of your help on getting this string to work. This is the string I eventually used.
SUMPRODUCT((LEFT(' AllClosed '!$A$1:$A$60,6)="200206")*(LEFT(' AllClosed '!$H$1:$H$60,4)="TEST"))
But I now need to add another variable, in other use a 3rd column. Can you help. I tried to add the string but I am only getting the #N/A or Valid#.
Thanks.

20020607, 16:08 #10
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cell Formulas (Office 2000)
Can you show us what you tried so we have a better idea what you are trying to do?
Legare Coleman

20020612, 14:03 #11
 Join Date
 May 2002
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cell Formulas (Office 2000)
Legare,
Thanks for your help. Attached is the file that I am working on. What I need is to develop a string that counts the number of occurrences of 'N' in the Ramp/Production column by meeting the criteria in both the QA Number column and Root Cause Column. I am currently using a string that sums based on two conditions. This is as follows:
=SUMPRODUCT((LEFT(' AllOpen '!A1:A995,6)="200204")*(LEFT(' AllOpen '!B1:B995,7)="Support")).
I thought by adding *(LEFT(' AllOpen '!C1:C995,1)="N") would work but it does not. Can you help! Thanks.

20020612, 16:03 #12
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cell Formulas (Office 2000)
It works fine for me once I put the correct number of spaces in the sheet name and adjusted the formula so that it matched all three criteria in the table in the workbook you attached. Did you array enter the formula (hold down the Ctrl and Shift keys) when you pressed the enter key to enter the formula?
See cell I3 in the attached workbook.Legare Coleman