Results 1 to 12 of 12
  1. #1
    New Lounger
    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.

  2. #2
    Uranium Lounger
    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

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

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

  5. #5
    New Lounger
    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.
    Attached Files Attached Files

  6. #6
    Uranium Lounger
    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.
    Attached Files Attached Files
    Legare Coleman

  7. #7
    3 Star Lounger
    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

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

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

  10. #10
    Uranium Lounger
    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

  11. #11
    New Lounger
    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.
    Attached Files Attached Files

  12. #12
    Uranium Lounger
    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.
    Attached Files Attached Files
    Legare Coleman

Posting Permissions

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