Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Array Formula, conditional on two cells (2003)

    Dear Loungers,

    Can someone show me how to do this, I know it should be easy but my head must be fried this morning!
    I have two columns each may contain a "Y" or nothing i.e. blank. I want to count the number of instances of "Y" in the first column and blank in the second. So e.g if A2=Y and B2=Y don't count; and A3=Y and B2=blank count it; and A4=blank and B4=blank don't count: the result in this case should be 1.

    should be easy?
    many thanks.......... liz

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

    Re: Array Formula, conditional on two cells (2003)

    The following is a "normal" formula (not an array formula):

    =SUMPRODUCT((A1:A100="Y")*(B1:B100=""))

    and this is an array formula (confirm with Ctrl+Shift+Enter):

    =SUM((A1:A100="Y")*(B1:B100=""))

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Array Formula, conditional on two cells (2003)

    Hans,

    Thank you but is this what I want? I undertsand what you have suggested but you are performing a multplication operation, I want to say if the first is "Y" and the second is blank count it (the row in effect). If I apply your formula I get zero for a "normal" formula and "N/A" for an array formula.

    liz

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

    Re: Array Formula, conditional on two cells (2003)

    The result of a logical expression such as A1="Y" is either TRUE = 1 or FALSE = 0. By summing the 1's, you effectively get a count. I have attached an example. The rows with "Y" in column A and a blank in column B are highlighted by conditional formatting.

    If you can't get it to work in your workbook, you might attach it (or a stripped down copy) to a reply.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula, conditional on two cells (2003)

    As the attached workbook shows, the formulas that Hans posted does exactly what you described in your first post (his formulas are in C1 and C2). If this doesn't help you figure out what you did wrong, then could you post your workbook with your formulas that are not working?
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Array Formula, conditional on two cells (2003)

    Ok I understand now and i can make it work, thank you.............. liz

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

    Re: Array Formula, conditional on two cells (2003)

    Remarkably similar! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula, conditional on two cells (2003)

    <img src=/S/yep.gif border=0 alt=yep width=15 height=15> I wonder how that happened? <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    Legare Coleman

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

    Re: Array Formula, conditional on two cells (2003)

    Great minds ... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Posting Permissions

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