Array Formulas

[Ben S]

I have been trying to use array formulas without any success. I haven't got the add in feature of conditional sum wizard and would like to know where i am going wrong.

I have got a number of columns and would like to count the no of eligible males. Column M3: M250 (eligibility) either is YES or No and Column AF3 :M250 (Gender) either is MALE or Female

I have written the formula
=Sum(($M$3:$M$250="YES")*($AF$3:$AF$250=MALE)

I should point out that i have add the braces{}using control, alt delete

 

[Andrew Poulsom]

Here is a normal (non array) formula:

=SUMPRODUCT(($M$3:$M$250="YES")*($AF$3:$AF$250="MALE"))

and an array formula:

=SUM(IF($M$3:$M$250="YES",IF($AF$3:$AF$250="MALE",1,0),0))

Use Ctrl+Shift+Enter not Ctrl+Alt+Delete!

 

[dk]

Hi,

You can use SUMPRODUCT to do this:-

=SUMPRODUCT((M3:M250="YES")*(AF3:AF250="MALE"))

 

[s-o-s]

On 2002-11-04 06:49, Ben S wrote:
I have been trying to use array formulas without any success. I haven't got the add in feature of conditional sum wizard and would like to know where i am going wrong.

I have got a number of columns and would like to count the no of eligible males. Column M3: M250 (eligibility) either is YES or No and Column AF3 :M250 (Gender) either is MALE or Female

I have written the formula
=Sum(($M$3:$M$250="YES")*($AF$3:$AF$250=MALE)

I should point out that i have add the braces{}using control, alt delete

Hi Ben,

You can do this without an array formula as follows:

=SUMPRODUCT(($M$3:$M$250="YES")*($AF$3:$AF$250="MALE"))

If you wanted to get an array formula this would work:

= SUM(IF($M$3:$M$250="YES",IF($AF$3:$AF$250="MALE",1,0),0))

The SUMPRODUCT would be my preferred route as you don't need the Ctrl + Shift + Enter and the problems for other people understanding etc...