Results 1 to 9 of 9

20060215, 11:48 #1
 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

20060215, 12:14 #2
 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=""))

20060215, 17:42 #3
 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

20060215, 17:51 #4
 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.

20060215, 17:52 #5
 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

20060215, 18:03 #6
 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

20060215, 18:11 #7
 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>

20060215, 18:48 #8
 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

20060215, 18:50 #9
 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>