Array formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I seem to be having a problem with an array formula. I have a spreadsheet
with names in one column, home department numbers in column B, earning codes
in column C, worked dept in column D and hours in column E. I'm trying to
sum only one type of earning code for each employee for the home dept so I'm
using this formula: {=sum((name=a2)*(dept=b2)*(earn_code=c2)*hours)} and all
I get is #NUM. I can't figure out what I am missing. Can anyone guide me?
Thanks.
 
Try this:
=SUMPRODUCT(--(name=a2)*(dept=b2)*(earn_code=c2)*hours)

Assuming the words name, dept, earn_code and hours refer to ranges, or named
ranges.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
I think your original formula should be:

=sum(IF(((name=a2)*(dept=b2)*(earn_code=c2)),hours,0))

type this in then press CTRL SHIFT and ENTER at the same time - the
curly brackets will be added automatically.

Pete
 
Yes, the words name, dept & earn_code are all named ranges. I tried your
example and I'm still getting the #NUM. What does the -- between the first
two paranthesis signify? Could my problem have anything to do with
formatting? Maybe there is a different way to go about getting a result?
Thanks much for your help.
 
I'm still getting the #NUM. I was wondering if formatting could have
anything to do with it. I know when I'm using lookup formulas the format is
one area I usually look at but I can't seem to find anything with this.
Thanks for your assistance.
 
The double-negative (--) is an Excel user convention that forces Excel to
convert a TRUE/FALSE value to +1 for TRUE, -1 for FALSE.

Regarding:
=SUMPRODUCT(--(name=a2)*(dept=b2)*(earn_code=c2)*hours)

I created those named ranges and entered values, but I can't get the formula
to return that error. Perhaps if you gave us a sample of the data that is in
those ranges we could figure out the issue.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Your error is being caused by multiplying text..."name=a2*dept=b2". You may
want to combine some of these data points in another column using "&", the do
a SUMIF based on the combined column.
 
That works! Thanks much.
--
Constance


JR said:
Your error is being caused by multiplying text..."name=a2*dept=b2". You may
want to combine some of these data points in another column using "&", the do
a SUMIF based on the combined column.
Click to expand...
 
Back
Top