Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Count Formula That Doesn't Work (Excel 2003)

    I have two array formulas, one that SUMS several figures based on two sets of conditions, and one that COUNTS several figures, based on the same conditions. When I enter the SUM as an array formula, it calculates correctly. However, when I try the same approach using the COUNT as an array, it does not return a valid answer. I have attached a sample file that illustrates the problem.

    Thanks in advance for any assistance on this.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Array Count Formula That Doesn't Work (Excel 2003)

    <P ID="edit" class=small>(Edited by JohnBF on 11-May-04 14:13. )</P>Edited.

    I expanded the values for better testing. It's easier to use:

    =SUMIF(B2:B4,"<"&B7)-SUMIF(B2:B4,"<"&B6)
    =COUNTIF(B2:B4,"<"&B7)-COUNTIF(B2:B4,"<"&B6)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Count Formula That Doesn't Work (Excel 2003)

    Well, John, you've come through once again. And as is so often the case with your solutions, it was relatively simple and quite elegant.

    Thanks again...

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Count Formula That Doesn't Work (Excel 2003)

    Would this work for you

    =SUMPRODUCT(--(B2:B4>B6),--(B2:B4<B7),B2:B4) to sum
    and
    =SUMPRODUCT(--(B2:B4>B6),--(B2:B4<B7)) to count?

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Count Formula That Doesn't Work (Excel 2003)

    Yes, it does work, but I'm not sure why. I tried your formula with one minus sign in front of each array and that worked as well. Could you explain how your formula works?

    Thanks very much for your response. I've found the SUMPRODUCT function to be very useful, did not see its use here until you pointed it out.

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Count Formula That Doesn't Work (Excel 2003)

    The -- is used as a coercer. It coerces true/false into 1/0. Similar in fashion to +0, *1. Sumproduct is a useful function for multi-conditional counting and summing. Conversely the -- can also coerce numbers formatted as text into real numbers.

Posting Permissions

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