Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: countif formula?

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's an abbreviated sample data from my sheet. There are 149 entries and I'm trying to pull out 6 for one of my summary sheet fields.

    Shift Class_name
    D1 Sigma
    D2 Sigma
    D1 Evaporator
    D2 Evaporator
    N1 Evaporator
    D1 Sigma

    If shift = "D1", then count number of cells containing "*Sigma*"

    I need to nest something, here's my current formula:
    =COUNTIF('Metals L2'!E$1:E$1131,"*sigma*")
    which returns all sigma, not filtering other shifts out. The variations I've tried have added or multipied the 2 variables.

    Am I in the right formula neigborhood?

    Thanks for any help on this.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-22 18:41, Spott wrote:
    If shift = "D1", then count number of cells containing "*Sigma*"

    I need to nest something, here's my current formula:
    =COUNTIF('Metals L2'!E$1:E$1131,"*sigma*")
    which returns all sigma, not filtering other shifts out. The variations I've tried have added or multipied the 2 variables.

    Am I in the right formula neigborhood?

    Thanks for any help on this.
    Assuming "Shift" is column A and "Class Name" is column B;
    Try this:
    =SUMPRODUCT((A2:A7="D2")*(B2:B7="sigma"))


    [ This Message was edited by: John McGraw on 2002-04-22 19:15 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So far no luck. These are the formulas I've tried:
    =COUNT(IF('Metals L2'!E$2:E$999="*sigma*",IF('Metals L2'!A$2:A$999="D1",0)))

    =SUM(('Metals L2'!E$2:E$999="*sigma*")*('Metals L2'!A$2:A$999="D1"))

    =SUMPRODUCT(('Metals L2'!A$2:A$999="D1")*('Metals L2'!E$2:E$999="*sigma*"))

    =SUM(('Metals L2'!E2:E999="*sigma*")*('Metals L2'!A2:A999="D1"))

    The result should be 6, these formulas all return 0. I think I entered them all as arrays (CSE)but my brain is getting very tired...
    Thanks!

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,022
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-04-23 12:53, Spott wrote:
    So far no luck. These are the formulas I've tried:
    =COUNT(IF('Metals L2'!E$2:E$999="*sigma*",IF('Metals L2'!A$2:A$999="D1",0)))

    =SUM(('Metals L2'!E$2:E$999="*sigma*")*('Metals L2'!A$2:A$999="D1"))

    =SUMPRODUCT(('Metals L2'!A$2:A$999="D1")*('Metals L2'!E$2:E$999="*sigma*"))

    =SUM(('Metals L2'!E2:E999="*sigma*")*('Metals L2'!A2:A999="D1"))

    The result should be 6, these formulas all return 0. I think I entered them all as arrays (CSE)but my brain is getting very tired...
    Thanks!
    Try:

    =SUMPRODUCT((ISNUMBER(SEARCH("sigma",'Metals L2'!E$2:E$999)))*('Metals L2'!A$2:A$999="D1"))

    Note 1. If "sigma" is not a substring occurring in different combinations, then as John suggested, just use:

    =SUMPRODUCT(('Metals L2'!E$2:E$999="sigma")*('Metals L2'!A$2:A$999="D1"))

    Note 2. You can put these criteria in cells of their own and use the cell refs in the formula.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-04-23 13:01 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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