Results 1 to 5 of 5
Thread: Do it in 1 step

20010208, 01:40 #1
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
Do it in 1 step
I saw a thread earlier that used an array formula like
=SUM(($A$1:$A$6<3)*(C$$1:$C$6=0))
to generate a new col of info where each entry was 0 or 1 depending on whether the 2 conditions are true.
For example with a1:a6 set as 1 2 1 3 2 3
and c1:c6 set as 0 3 6 8 0 0
the above formula, when entered as an array formula for 6 cells, would return 6 entries as 0 or 1.
I now want to know how many 1's are in the new col. Well we could just sum that up  no problem.
What if I just wanted the sum and didn't want to generate an extra col (bcs I have lots of cols with data) and then sum it? Can I do it in Excel w/o VBA? Array formulas were never my strong point. I think I can imagine the VBA easily enough but any hints in the right direction would be helpful if there's no other way to do it.
Thks.
Fred

20010208, 01:53 #2
 Join Date
 Dec 2000
 Location
 Queanbeyan, New South Wales, Australia
 Posts
 3,730
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Do it in 1 step
Fred,
I rhink this is the thread you're referring to.
You're almost there by yourself. Enter the formula (you had one $ sign out of place):
=SUM(($A$1:$A$6<3)*($C$1:$C$6=0))but while you're still in edit mode for the cell, press Crtl, Shift, Enter (to make it an array formula)
You have your answer.Subway Belconnen home of the Signboard to make you smile. Get (almost) daily updates follow SubwayBelconnen on Twitter.

20010208, 04:40 #3
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: Do it in 1 step
Geoff,
Thks for the quick response.
As far as the misplaced $ sign, that's bcs my Excel formula didn't have $ in all the places (only in front of numbers) and I just added them in during the posting. Obviously a typo.
As I said in my original post, I not totally up on array formulas. I think I was using CTRL+Enter to get the extra col of 1's, with 6 cells in that col selected. So the formula was evaluated for each of the 6 rows. However, I only entered the formula once but CTRL+Enter propogated the formula to all the selected cells.
With your proposal, I only had 1 cell selected. When I hit SHIFT+CTRL+enter, I get the sum as I wanted. With only CTRL+Enter and the 6 cols selected, I get 1 or 0. If I don't use the sum, but with 6 cols selected, I also get six 1's or 0's with CTRL+Enter. With just plain Enter, I get only 1 number.
So I'm a little confused about the differences. Help!
Thks.
Fred

20010208, 05:08 #4
 Join Date
 Dec 2000
 Location
 Queanbeyan, New South Wales, Australia
 Posts
 3,730
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Do it in 1 step
Fred,
I'm not a great person to explain array formulae.
Have a look at the Microsoft help topic on About array formulas and how to enter them to get a bit of an overview.
It's always a good copout to point someone at Microsoft help!Subway Belconnen home of the Signboard to make you smile. Get (almost) daily updates follow SubwayBelconnen on Twitter.

20010208, 07:53 #5
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Do it in 1 step
If you do not have Ctrl+Shift held down when you press Enter, then the formula will not be an array formula. You would see this because Excel does not put the curly brackets {} around the formula (just typing the curly brackets around a formula will not make it an array formula either). I am not positive how Excel would evaluate the formula you are using if it is not entered as an array formula, but most likely it will only evaluate the formula for the first cell in each range that you specified in the formula. That would result in the formula evaluating to either 0 or 1.
What cells you have selected when you press Ctrl+Shift+Enter makes no difference. The Ctrl+Shift+Enter is only going to affect the cell into which the formula is being entered. When entering a formula, selecting other cells only enters the selected range into the formula.
When you array enter the formula you are using with the Sum in it, Excel still generates the array of 0s and 1s. However, the Sum function then adds up those 0s and 1s producing a single number final result.Legare Coleman