conditional sum - number of arguments problem
 

I have a conditonal sum that currently works fine as follows;

{=SUM(IF(Sheet1!$I$3:$I$5000="ProductType",IF(Shee t1!$K$3:$K$5000="T0",
IF(Sheet1!$O$3:$O$50001,Sheet1!$F$3:$F$5000,0),0) ,0))}

What I am now trying to do is to add another argument to filter the years
which are in column A in general format, something like;
IF(Sheet1!$A$3:$A$5000="2005",,)

When I use the Conditional sum wizard it works when I enter any combination
of two arguments, but when I try using all three I get the error;

"Run Time error '1004' Unable to set the ForulaArray property of the Range
class"

Can anybody help me to solve this, or can you only use two arguments with
the Conditional Sum wizard?

thanks, Les.

conditional sum - number of arguments problem
 

Why not just enter it yourself

=SUM(IF(Sheet1!$A$3:$A$5000="2005",IF(Sheet1!$I$3: $I$5000="ProductType",IF(S
heet1!$K$3:$K$5000="T0",
IF(Sheet1!$O$3:$O$50001,Sheet1!$F$3:$F$5000,0),0) ,0),0))

remembering to array enter it, Ctrl-Shift-Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"LesLdh" wrote in message
...
I have a conditonal sum that currently works fine as follows;

{=SUM(IF(Sheet1!$I$3:$I$5000="ProductType",IF(Shee t1!$K$3:$K$5000="T0",
IF(Sheet1!$O$3:$O$50001,Sheet1!$F$3:$F$5000,0),0) ,0))}

What I am now trying to do is to add another argument to filter the years
which are in column A in general format, something like;
IF(Sheet1!$A$3:$A$5000="2005",,)

When I use the Conditional sum wizard it works when I enter any
combination
of two arguments, but when I try using all three I get the error;

"Run Time error '1004' Unable to set the ForulaArray property of the Range
class"

Can anybody help me to solve this, or can you only use two arguments with
the Conditional Sum wizard?

thanks, Les.

conditional sum - number of arguments problem
 

Thanks for the reply Bob, I have tried entering the formula myself but it
doesn't give the expected result. I have the following values in Sheet1;
A3="2005", I3="ProductType", K3="T0" and F3=1 The expected result should
therefore be 1, but it is returning a result of 0 (zero). Does it make a
difference that the fomula is on Sheet2 and the data is on Sheet1?

Les.


"Bob Phillips" wrote:

Why not just enter it yourself

=SUM(IF(Sheet1!$A$3:$A$5000="2005",IF(Sheet1!$I$3: $I$5000="ProductType",IF(S
heet1!$K$3:$K$5000="T0",
IF(Sheet1!$O$3:$O$50001,Sheet1!$F$3:$F$5000,0),0) ,0),0))

remembering to array enter it, Ctrl-Shift-Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"LesLdh" wrote in message
...
I have a conditonal sum that currently works fine as follows;

{=SUM(IF(Sheet1!$I$3:$I$5000="ProductType",IF(Shee t1!$K$3:$K$5000="T0",
IF(Sheet1!$O$3:$O$50001,Sheet1!$F$3:$F$5000,0),0) ,0))}

What I am now trying to do is to add another argument to filter the years
which are in column A in general format, something like;
IF(Sheet1!$A$3:$A$5000="2005",,)

When I use the Conditional sum wizard it works when I enter any
combination
of two arguments, but when I try using all three I get the error;

"Run Time error '1004' Unable to set the ForulaArray property of the Range
class"

Can anybody help me to solve this, or can you only use two arguments with
the Conditional Sum wizard?

thanks, Les.

conditional sum - number of arguments problem
 

Probably because the 2005 is a number, and by enclosing it in quotes you are
testing for text, Try

=SUM(IF(Sheet1!$A$3:$A$5000=2005,
IF(Sheet1!$I$3:$I$5000="ProductType",
IF(Sheet1!$K$3:$K$5000="T0",
IF(Sheet1!$O$3:$O$50001,Sheet1!$F$3:$F$5000,0),0) ,0),0))

still array-entered

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"LesLdh" wrote in message
...
Thanks for the reply Bob, I have tried entering the formula myself but it
doesn't give the expected result. I have the following values in Sheet1;
A3="2005", I3="ProductType", K3="T0" and F3=1 The expected result should
therefore be 1, but it is returning a result of 0 (zero). Does it make a
difference that the fomula is on Sheet2 and the data is on Sheet1?

Les.


"Bob Phillips" wrote:

Why not just enter it yourself


=SUM(IF(Sheet1!$A$3:$A$5000="2005",IF(Sheet1!$I$3: $I$5000="ProductType",IF(S
heet1!$K$3:$K$5000="T0",
IF(Sheet1!$O$3:$O$50001,Sheet1!$F$3:$F$5000,0),0) ,0),0))

remembering to array enter it, Ctrl-Shift-Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"LesLdh" wrote in message
...
I have a conditonal sum that currently works fine as follows;


{=SUM(IF(Sheet1!$I$3:$I$5000="ProductType",IF(Shee t1!$K$3:$K$5000="T0",
IF(Sheet1!$O$3:$O$50001,Sheet1!$F$3:$F$5000,0),0) ,0))}

What I am now trying to do is to add another argument to filter the
years
which are in column A in general format, something like;
IF(Sheet1!$A$3:$A$5000="2005",,)

When I use the Conditional sum wizard it works when I enter any
combination
of two arguments, but when I try using all three I get the error;

"Run Time error '1004' Unable to set the ForulaArray property of the
Range
class"

Can anybody help me to solve this, or can you only use two arguments
with
the Conditional Sum wizard?

thanks, Les.

conditional sum - number of arguments problem
 

Works perfectly...thanks Bob

"Bob Phillips" wrote:

Probably because the 2005 is a number, and by enclosing it in quotes you are
testing for text, Try

=SUM(IF(Sheet1!$A$3:$A$5000=2005,
IF(Sheet1!$I$3:$I$5000="ProductType",
IF(Sheet1!$K$3:$K$5000="T0",
IF(Sheet1!$O$3:$O$50001,Sheet1!$F$3:$F$5000,0),0) ,0),0))

still array-entered

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"LesLdh" wrote in message
...
Thanks for the reply Bob, I have tried entering the formula myself but it
doesn't give the expected result. I have the following values in Sheet1;
A3="2005", I3="ProductType", K3="T0" and F3=1 The expected result should
therefore be 1, but it is returning a result of 0 (zero). Does it make a
difference that the fomula is on Sheet2 and the data is on Sheet1?

Les.


"Bob Phillips" wrote:

Why not just enter it yourself


=SUM(IF(Sheet1!$A$3:$A$5000="2005",IF(Sheet1!$I$3: $I$5000="ProductType",IF(S
heet1!$K$3:$K$5000="T0",
IF(Sheet1!$O$3:$O$50001,Sheet1!$F$3:$F$5000,0),0) ,0),0))

remembering to array enter it, Ctrl-Shift-Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"LesLdh" wrote in message
...
I have a conditonal sum that currently works fine as follows;


{=SUM(IF(Sheet1!$I$3:$I$5000="ProductType",IF(Shee t1!$K$3:$K$5000="T0",
IF(Sheet1!$O$3:$O$50001,Sheet1!$F$3:$F$5000,0),0) ,0))}

What I am now trying to do is to add another argument to filter the
years
which are in column A in general format, something like;
IF(Sheet1!$A$3:$A$5000="2005",,)

When I use the Conditional sum wizard it works when I enter any
combination
of two arguments, but when I try using all three I get the error;

"Run Time error '1004' Unable to set the ForulaArray property of the
Range
class"

Can anybody help me to solve this, or can you only use two arguments
with
the Conditional Sum wizard?

thanks, Les.