Conditional sum with multiple criteria

[Jamie Martin]

Hi,

I am trying to find a conditional sum. As best I can tell, my array formula
follows John Walkenbach's method on page 230 of the Excel 2002 Bible. But I
get a #VALUE! error.

Here is the formula:

{=SUM((Discipline_division<>"Men")*(Discipline_division<>"Women")*_1970__71)
}

I want to add only those numbers in the column named "_1970__71" which do
not have "Men" or "Women" in the "Discipline_division" column. The two sets
of nested parens should each return a Boolean value, and should multiply to
0 unless both are true, in which case they multiply to 1. What am I doing
wrong?

Thanks,

Jamie

 

[Paul Corrado]

Jamie,

One possibility is that the named ranges contain the column headings, which
are text and would lead to a #VALUE error.

Also

SUMPRODUCT((Discipline_division<>"Men")*(Discipline_division<>"Women")*_1970
__71)

Will sum the range based on your criteria without using an array entry.

PC

 

[EZ Money]

I feel it would help if you have a range named Psychology and one named
English; the Discipline reference would then be unnecessary.