# Thread: Array Formula, conditional on two cells (2003)

1. ## 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. ## 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. ## 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. ## 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. ## 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?

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

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

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

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

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

9. ## 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
•