# Thread: Cell Formulas (Office 2000)

1. ## 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.

3. ## 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!

4. ## 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.

5. ## 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.

6. ## 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.

7. ## Re: Cell Formulas (Office 2000)

Also

=SUMPRODUCT(ISNUMBER(SEARCH("200108",A3:A34))*ISNU MBER(SEARCH("Test",B3:B34)))

will produce the desire count.

8. ## 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

9. ## 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.

10. ## Re: Cell Formulas (Office 2000)

Can you show us what you tried so we have a better idea what you are trying to do?

11. ## 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.

12. ## 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•