Array Problems - #NUM error

  • Thread starter Thread starter A Nony Mouse
  • Start date Start date
A

A Nony Mouse

Hi all...

I'm a long time excel user, but I would far from call myself a 'power user'.
In my new job, I'm responsible for tracking more and more data and using
excel as a reporting tool.

I bought the Excel2003 Bible (Walkenbach) for guidance and I'm having some
trouble with using arrays.

I have a spreadsheet with student names, gender, ethnicity, and their
'status' at our institution (in columns). I need to -count- those students
who meet multiple criteria... For example, all the females who are admitted
or enrolled and are African American. According to the 'Bible' (and my
programming background), I'd probably use an array something like this:

{=SUM((Gender="F")*(Ethnicity="AA")*((Status="Enrolled")+(Status="Admitted")
))}

When I enter the array (yes, I know not to enter the {}s) with
CNTRL-SHFT-ENTER, I always get a #NUM! error in the cell. It seems like the
array wants numerical inputs for the field values... According to all the
printed examples in the 'Bible', however, this array should work.

Anyone have a clue what might be wrong??

Help!! Mouse
 
Hi
you may try the following (it's a non array formula)
=SUMPRODUCT((Gender="F")*(Ethnicity="AA")*((Status="Enrolled")+(Status=
"Admitted")>0))
Defined your names like the following:
Gender: A1:A1000 (note a definition like A:A is not allowed for
SUMPRODUCT)
 
Frank,

You rock! That worked.

Do you have any clue as to why the array function didn't work? I only ask
because I'm going to be pulling in data sets that vary *greatly* in size.
Ideally, I'd like to set full columns as 'Gender', 'Status', etc. just so I
can paste in new data to the data table and have all the formulas update.

Thanks, Mouse>
 
If you pull 'large' columns just use
SUMPRODUCT((A1:A50000).....)

In respect to your array formula maybe the following would work (not
tested though)
=SUM(IF((Gender="F")*(Ethnicity="AA")*((Status="Enrolled")+(Status="Adm
itted"));1;0)
entered as array formula
 
Are any of the following the case with your data:-

1) Ranges are not all the same size
2) Ranges defined as entire columns, eg A:A or B:B etc

If so then it will not work.

Formula works fine for me exactly as you have quoted it.
 
Ken,

Sorry for getting back to you so slowly... I got sidetracked by other work.

My guess is that perhaps one of the ranges was a different size... I'll
give it a try tonight and report back tomorrow.

Thanks, Brian
 
Back
Top