Cell Formulas (Office 2000)

Topic

New Reply

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.

Reply | Quote

Replies

Check out THIS THREAD.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

The following formula, array entered (hold down Ctrl+Shift when you press enter) looks like it does what you asked:

=SUM((LEFT('    AllOpen   '!A3:A34,6)="200108")*NOT(ISERROR(SEARCH("Test",'    AllOpen   '!B3:B34))))

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.

Reply | Quote

Also

=SUMPRODUCT(ISNUMBER(SEARCH(“200108”,A3:A34))*ISNUMBER(SEARCH(“Test”,B3:B34)))

will produce the desire count.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote