SUMIF
 

I can not get this to give me a value other then 0

{=SUM(IF('reference data'!$E$2:$E$125,"=1",IF('reference data'!$I$2:$I$125,
"=1",'reference data'!$A$2:$A$125)))}

I got this from the excel help
An array formula can perform multiple calculations and then return either a
single result or multiple results. Array formulas act on two or more sets of
values known as array arguments. Each array argument must have the same
number of rows and columns. You create array formulas the same way that you
create basic, single-value formulas. Select the cell or cells that will
contain the formula, create the formula, and then press CTRL+SHIFT+ENTER to
enter the formula.
the brackets are added once I do the ctrl shift enter


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1

try =1 instead of "=1"

--
Don Guillett
SalesAid Software

"CCTD via OfficeKB.com" <u14229@uwe wrote in message
news:55be7dc70cc63@uwe...
I can not get this to give me a value other then 0

{=SUM(IF('reference data'!$E$2:$E$125,"=1",IF('reference
data'!$I$2:$I$125,
"=1",'reference data'!$A$2:$A$125)))}

I got this from the excel help
An array formula can perform multiple calculations and then return either
a
single result or multiple results. Array formulas act on two or more sets
of
values known as array arguments. Each array argument must have the same
number of rows and columns. You create array formulas the same way that
you
create basic, single-value formulas. Select the cell or cells that will
contain the formula, create the formula, and then press CTRL+SHIFT+ENTER
to
enter the formula.
the brackets are added once I do the ctrl shift enter


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200510/1

Assuming that Column E and Column I contain numerical values, try...

=SUM(IF('reference data'!$E$2:$E$125=1,IF('reference
data'!$I$2:$I$125=1,'reference data'!$A$2:$A$125)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <55be7dc70cc63@uwe, "CCTD via OfficeKB.com" <u14229@uwe
wrote:

I can not get this to give me a value other then 0

{=SUM(IF('reference data'!$E$2:$E$125,"=1",IF('reference data'!$I$2:$I$125,
"=1",'reference data'!$A$2:$A$125)))}

I got this from the excel help
An array formula can perform multiple calculations and then return either a
single result or multiple results. Array formulas act on two or more sets of
values known as array arguments. Each array argument must have the same
number of rows and columns. You create array formulas the same way that you
create basic, single-value formulas. Select the cell or cells that will
contain the formula, create the formula, and then press CTRL+SHIFT+ENTER to
enter the formula.
the brackets are added once I do the ctrl shift enter