Array formula help
 

Currently I have
Individual Options
Name =SUMIF(Options!$A$8:$A$134,B4,Options!$B$8:$B$134)
which works fine.

I am trying to add an extra detail to a summary sheet for the exercise
price of options, so the sheet would look like:
Name Price Price Price Total


I am not sure why this array formula keeps returning #VALUE# for the
price breakout
=SUM((Options!$A$8:$A$134=B4)*(Options!$D$8:$D$134 =C3)*Options!$B$8:$B$134)
or
=SUM((Options!$A$8:$A$134=B4)*(Options!$D$8:$D$134 =C3)*(Options!$B$8:$B$134))

Detail spreadsheet = "Options"
(Options!$A$8:$A$134=B4) A8:A134 is the transaction data with Col B
being the employee B4 is the corresponding employee on the summary sheet.

(Options!$D$8:$D$134=C3) is the exercise price for the transaction c3 =
price on summary sheet.

Any advise on why the formula is returning #Value#

Thanks,
Scott

You need to enter it with ctrl + shift & enter
it can be replaced with

=SUMPRODUCT(--(Options!$A$8:$A$134=B4),--(Options!$D$8:$D$134=C3),Options!$B$8:$B$134)

entered normally


Regards,

Peo Sjoblom

"scott" wrote:

Currently I have
Individual Options
Name =SUMIF(Options!$A$8:$A$134,B4,Options!$B$8:$B$134)
which works fine.

I am trying to add an extra detail to a summary sheet for the exercise
price of options, so the sheet would look like:
Name Price Price Price Total


I am not sure why this array formula keeps returning #VALUE# for the
price breakout
=SUM((Options!$A$8:$A$134=B4)*(Options!$D$8:$D$134 =C3)*Options!$B$8:$B$134)
or
=SUM((Options!$A$8:$A$134=B4)*(Options!$D$8:$D$134 =C3)*(Options!$B$8:$B$134))

Detail spreadsheet = "Options"
(Options!$A$8:$A$134=B4) A8:A134 is the transaction data with Col B
being the employee B4 is the corresponding employee on the summary sheet.

(Options!$D$8:$D$134=C3) is the exercise price for the transaction c3 =
price on summary sheet.

Any advise on why the formula is returning #Value#

Thanks,
Scott

Try

=SUMPRODUCT(--(Options!$A$8:$A$134=B4),--(Options!$D$8:$D$134=C3),Options!$B
$8:$B$134))

which is not an array formula


--

HTH

RP
(remove nothere from the email address if mailing direct)


"scott" wrote in message
...
Currently I have
Individual Options
Name =SUMIF(Options!$A$8:$A$134,B4,Options!$B$8:$B$134)
which works fine.

I am trying to add an extra detail to a summary sheet for the exercise
price of options, so the sheet would look like:
Name Price Price Price Total


I am not sure why this array formula keeps returning #VALUE# for the
price breakout

=SUM((Options!$A$8:$A$134=B4)*(Options!$D$8:$D$134 =C3)*Options!$B$8:$B$134)
or

=SUM((Options!$A$8:$A$134=B4)*(Options!$D$8:$D$134 =C3)*(Options!$B$8:$B$134)
)

Detail spreadsheet = "Options"
(Options!$A$8:$A$134=B4) A8:A134 is the transaction data with Col B
being the employee B4 is the corresponding employee on the summary
sheet.

(Options!$D$8:$D$134=C3) is the exercise price for the transaction c3 =
price on summary sheet.

Any advise on why the formula is returning #Value#

Thanks,
Scott

Thanks. I tried that.

I think I found the problem- there was text in the column C causing both
the array and the sumproduct formulas to not work.


Bob Phillips wrote:

Try

=SUMPRODUCT(--(Options!$A$8:$A$134=B4),--(Options!$D$8:$D$134=C3),Options!$B
$8:$B$134))

which is not an array formula