Results 1 to 5 of 5

Thread: Do it in 1 step

  1. #1
    Silver Lounger
    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

  2. #2
    Platinum Lounger
    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.

  3. #3
    Silver Lounger
    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

  4. #4
    Platinum Lounger
    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.

  5. #5
    Uranium Lounger
    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

Posting Permissions

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