[Harix]

Hi,

I'm trying to put up this formula:

=SUM(IF((G17:G31="Y"),($C17:$C31*80),0))

so that it multiplies with 80 and then sums all those values C17 through C31
for which values of G17 through G31 are Y.

But this gives me a value error in excel. When I remove the range and enter
a single cell, it works fine. I'm not sure what I'm doing wrong here.

[T. Valko]

The formula as written is an array formula. You probably didn't enter it as
an array formula.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formulas in the brackets. You
can't just type these brackets in, you *must* use the key combo. Also,
anytime you edit an array formula it *must* be re-entered as an array using
the key combo.

You can write it like this:

=SUM(IF(G17:G31="Y",$C17:$C31*80))

However, you can also do this with a non-array formula (just hit ENTER):

=SUMIF(G17:G31,"Y",C17:C31)*80

--
Biff
Microsoft Excel MVP


"Harix" wrote in message
...
Hi,

I'm trying to put up this formula:

=SUM(IF((G17:G31="Y"),($C17:$C31*80),0))

so that it multiplies with 80 and then sums all those values C17 through
C31
for which values of G17 through G31 are Y.

But this gives me a value error in excel. When I remove the range and
enter
a single cell, it works fine. I'm not sure what I'm doing wrong here.

[David Biddulph[_2_]]

Perhaps you forgot to enter it as an array formula?

Also, you can lose some of the parentheses.
=SUM(IF(G17:G31="Y",$C17:$C31*80,0)) as an array formula will do.
--
David Biddulph


Harix wrote:
Hi,

I'm trying to put up this formula:

=SUM(IF((G17:G31="Y"),($C17:$C31*80),0))

so that it multiplies with 80 and then sums all those values C17
through C31 for which values of G17 through G31 are Y.

But this gives me a value error in excel. When I remove the range and
enter a single cell, it works fine. I'm not sure what I'm doing wrong
here.

[Rick Rothstein]

Select the cell with that formula, click into the Formula Bar and then press
Ctrl+Shift+Enter (you have an array formula and it requires that keystroke
combination to commit it).

--
Rick (MVP - Excel)


"Harix" wrote in message
...
Hi,

I'm trying to put up this formula:

=SUM(IF((G17:G31="Y"),($C17:$C31*80),0))

so that it multiplies with 80 and then sums all those values C17 through
C31
for which values of G17 through G31 are Y.

But this gives me a value error in excel. When I remove the range and
enter
a single cell, it works fine. I'm not sure what I'm doing wrong here.

[Harix]

Valko,

Thanks a bunch for the quick and detailed response! it worked! have a nice
day!

Haris

"T. Valko" wrote:

The formula as written is an array formula. You probably didn't enter it as
an array formula.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formulas in the brackets. You
can't just type these brackets in, you *must* use the key combo. Also,
anytime you edit an array formula it *must* be re-entered as an array using
the key combo.

You can write it like this:

=SUM(IF(G17:G31="Y",$C17:$C31*80))

However, you can also do this with a non-array formula (just hit ENTER):

=SUMIF(G17:G31,"Y",C17:C31)*80

--
Biff
Microsoft Excel MVP


"Harix" wrote in message
...
Hi,

I'm trying to put up this formula:

=SUM(IF((G17:G31="Y"),($C17:$C31*80),0))

so that it multiplies with 80 and then sums all those values C17 through
C31
for which values of G17 through G31 are Y.

But this gives me a value error in excel. When I remove the range and
enter
a single cell, it works fine. I'm not sure what I'm doing wrong here.

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Harix" wrote in message
...
Valko,

Thanks a bunch for the quick and detailed response! it worked! have a nice
day!

Haris

"T. Valko" wrote:

The formula as written is an array formula. You probably didn't enter it
as
an array formula.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula
you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT
key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formulas in the brackets. You
can't just type these brackets in, you *must* use the key combo. Also,
anytime you edit an array formula it *must* be re-entered as an array
using
the key combo.

You can write it like this:

=SUM(IF(G17:G31="Y",$C17:$C31*80))

However, you can also do this with a non-array formula (just hit ENTER):

=SUMIF(G17:G31,"Y",C17:C31)*80

--
Biff
Microsoft Excel MVP


"Harix" wrote in message
...
Hi,

I'm trying to put up this formula:

=SUM(IF((G17:G31="Y"),($C17:$C31*80),0))

so that it multiplies with 80 and then sums all those values C17
through
C31
for which values of G17 through G31 are Y.

But this gives me a value error in excel. When I remove the range and
enter
a single cell, it works fine. I'm not sure what I'm doing wrong here.