Results 1 to 4 of 4
  1. #1
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Simple array formula? (Excel 2003 SP2)

    I suspect I can do this with a simple array formula, but I need a bit of help working out how to do it.

    Cells I11:I65535 contain "Yes" or "No" (or blank)
    Cells K11:K65535 contain "Normal" or "Difficult"
    These are based on data validation from lists in the first few rows of the worksheet

    I can count how many Yes's and No's I have using Countif formulae
    How do I count the number of Yes-Difficult and Yes-Normal combinations?

    StuartR

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple array formula? (Excel 2003 SP2)

    You can use:
    <code>
    =SUMPRODUCT((I1:I65535="Yes")*(K1:K65535="Difficul t"))
    </code>
    as a "normal" formula, or
    <code>
    =SUM((I1:I65535="Yes")*(K1:K65535="Difficult"))
    </code>
    as an array formula.

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Simple array formula? (Excel 2003 SP2)

    Thanks.

    I'm sure I had tried that syntax, but it worked perfectly when I copied and pasted your example.

    StuartR

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Simple array formula? (Excel 2003 SP2)

    Hi Stuart,

    FWIW, with array-type formula, you can't simply use whole-of-row or whole-of-column references - you need to explicitly use fully qualified cell references for both ends of the array.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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