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.
--
Constance


"Ron Coderre" wrote:

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


"Constance" wrote:

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.
--
Constance

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


"Constance" wrote:

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.
--
Constance


"Ron Coderre" wrote:

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


"Constance" wrote:

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.
--
Constance

I received another way of going about it that works but thanks again for your
assistance.
--
Constance


"Ron Coderre" wrote:

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


"Constance" wrote:

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.
--
Constance


"Ron Coderre" wrote:

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


"Constance" wrote:

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.
--
Constance