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

Topic

New Reply

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.

Reply | Quote

Replies

(Edited by JohnBF on 11-May-04 14:13. )

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)

Reply | Quote

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…

Reply | Quote

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…

Reply | Quote

Would this work for you

=SUMPRODUCT(–(B2:B4>B6),–(B2:B4B6),–(B2:B4<B7)) to count?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Would this work for you

=SUMPRODUCT(–(B2:B4>B6),–(B2:B4B6),–(B2:B4<B7)) to count?

Reply | Quote

(Edited by JohnBF on 11-May-04 14:13. )

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)

Reply | Quote