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

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

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