IF statement on a range of cells in Excel 2007

[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

 

[David Biddulph]

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.

 

[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).

 

[Harix]

Valko,

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

Haris

 

[T. Valko]

You're welcome. Thanks for the feedback!