I am using excel for preparing a schedule of Doors. For sorting the data i
have used the following formula
SUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF($K$1:$K $2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$2263,0),0) ,0),0))
which returns a #Value Error.
If i evaluate this formula through Insert<Fuction, i am getting the desired
result, if i evaluate the formula through formula auditing i get the error,
is this a bug in the programme.
The problem is in the if statement, Excel is not evaluating the condition
D2:D2263=1 as a whole but is only evaluating d2:d2263

Any help would be greatly appreciated.

Thank you.

try
SUMproduct(D2:D2263=1)*($H$1:$H$2263=E2269)*($K$1: $K$2263=G2269)*($E$1:$E$2263="A")*$C$2:$C$2263)--Don GuillettSalesAid "Mayur Godbole" <Mayur wrote in ...I am using excel for preparing a schedule of Doors. For sorting the data i have used the following formulaSUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF ($K$1:$K$2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$22 63,0),0),0),0)) which returns a #Value Error. If i evaluate this formula through Insert<Fuction, i am getting thedesired result, if i evaluate the formula through formula auditing i get theerror, is this a bug in the programme. The problem is in the if statement, Excel is not evaluating the condition D2:D2263=1 as a whole but is only evaluating d2:d2263 Any help would be greatly appreciated. Thank you.

SUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF($K$1:$K
$2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$2263,0),0) ,0),0))

I assume it was entered as an array as well

why not use

SUMproduct((D2:D2263=1)*($H$2:$H$2263=E2269)*($K$2 :$K
$2263=G2269)*($E$1:$E$2263="A")*($C$2:$C$2263))

incidently i noticed some of your ranges are 2:2263 and some are 1:2263
they should be consistent, i have made them all 2:2263 above


Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=556485

Even that does not work geting the same error

"Don Guillett" wrote:

try
SUMproduct(D2:D2263=1)*($H$1:$H$2263=E2269)*($K$1: $K$2263=G2269)*($E$1:$E$2263="A")*$C$2:$C$2263)--Don GuillettSalesAid "Mayur Godbole" <Mayur wrote in ...I am using excel for preparing a schedule of Doors. For sorting the data i have used the following formulaSUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF ($K$1:$K$2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$22 63,0),0),0),0)) which returns a #Value Error. If i evaluate this formula through Insert<Fuction, i am getting thedesired result, if i evaluate the formula through formula auditing i get theerror, is this a bug in the programme. The problem is in the if statement, Excel is not evaluating the condition D2:D2263=1 as a whole but is only evaluating d2:d2263 Any help would be greatly appreciated. Thank you.

The formula im using is to give me a total after evaluating all the
conditions in a table. This helps me sort out data based on all the TRUE
criteria

"Mayur Godbole" wrote:

I am using excel for preparing a schedule of Doors. For sorting the data i
have used the following formula
SUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF($K$1:$K $2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$2263,0),0) ,0),0))
which returns a #Value Error.
If i evaluate this formula through Insert<Fuction, i am getting the desired
result, if i evaluate the formula through formula auditing i get the error,
is this a bug in the programme.
The problem is in the if statement, Excel is not evaluating the condition
D2:D2263=1 as a whole but is only evaluating d2:d2263

Any help would be greatly appreciated.

Thank you.

Tha Gave me a #NA error

"Dav" wrote:


SUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF($K$1:$K
$2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$2263,0),0) ,0),0))

I assume it was entered as an array as well

why not use

SUMproduct((D2:D2263=1)*($H$2:$H$2263=E2269)*($K$2 :$K
$2263=G2269)*($E$1:$E$2263="A")*($C$2:$C$2263))

incidently i noticed some of your ranges are 2:2263 and some are 1:2263
they should be consistent, i have made them all 2:2263 above


Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=556485

As suggested, correct all ranges to be same.
SUMproduct(D2:D2269=1)*($H$1:$H$2263=E2269)*($K$1: $K$2269=G2269)*($E$1:$E$2269="A")*$C$2:$C$2269)If you want a TOTAL of col c based on the other criteria then this will giveit to you.If you want a COUNT then eliminate col c criteria SUMproduct(D2:D2269=1)*($H$1:$H$2263=E2269)*($K$1: $K$2269=G2269)*($E$1:$E$2269="A"))or decide what col C should match and add a para at the end SUMproduct(D2:D2269=1)*($H$1:$H$2263=E2269)*($K$1: $K$2269=G2269)*($E$1:$E$2269="A")*$C$2:$C$2269=wha t))--Don GuillettSalesAid "Mayur Godbole" wrote in ... The formula im using is to give me a total after evaluating all the conditions in a table. This helps me sort out data based on all the TRUE criteria "Mayur Godbole" wrote: I am using excel for preparing a schedule of Doors. For sorting the datai have used the following formulaSUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,I F($K$1:$K$2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$2 263,0),0),0),0)) which returns a #Value Error. If i evaluate this formula through Insert<Fuction, i am getting thedesired result, if i evaluate the formula through formula auditing i get theerror, is this a bug in the programme. The problem is in the if statement, Excel is not evaluating the condition D2:D2263=1 as a whole but is only evaluating d2:d2263 Any help would be greatly appreciated. Thank you.

This is my dilema
I have 6 colums of data extending to some 2000 rows. I want to sort out data
in each column and consolidate it. The formula must be able to look into
column one wherever the condition is satisfied look into the adjoining column
and so on.....its like conditional addition add-in.
The formula I inserted is failing to evaluate the if formula as a whole and
only is looking at the column range and giving me the error.

A simple example to explain my problem:

ColC Col A Col B
1 2 3
1 2 3
1 1 3
1 3 2

=sum(if(colA1:colA4=2,if(colB1:colB4=3,colC1:colC4 ,0),0))

when i evaluate this using function i get the appropriate result
when i evaluate this using the formula auditing i get an error

In function it evaluates the first If condition as ColA1:ColA4=2 as a whole
In formula auditing it evaluates the same as ColA1:ColA4 only and returns
error




"Mayur Godbole" wrote:

Tha Gave me a #NA error

"Dav" wrote:


SUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF($K$1:$K
$2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$2263,0),0) ,0),0))

I assume it was entered as an array as well

why not use

SUMproduct((D2:D2263=1)*($H$2:$H$2263=E2269)*($K$2 :$K
$2263=G2269)*($E$1:$E$2263="A")*($C$2:$C$2263))

incidently i noticed some of your ranges are 2:2263 and some are 1:2263
they should be consistent, i have made them all 2:2263 above


Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=556485

Thank You Guys Im still trying to figure it out and keep getting an error. I
have tried using the solutions you have provided but keep getting an error.
Thank you anyways.

"Mayur Godbole" wrote:

I am using excel for preparing a schedule of Doors. For sorting the data i
have used the following formula
SUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF($K$1:$K $2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$2263,0),0) ,0),0))
which returns a #Value Error.
If i evaluate this formula through Insert<Fuction, i am getting the desired
result, if i evaluate the formula through formula auditing i get the error,
is this a bug in the programme.
The problem is in the if statement, Excel is not evaluating the condition
D2:D2263=1 as a whole but is only evaluating d2:d2263

Any help would be greatly appreciated.

Thank you.

It looks like you're trying a conditional sum to evaluate an array. Did you
try holding down the Ctrl-Shift keys when you press <Enter? Your formula
will be enclosed in brackets:

{=
SUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF($K$1:$K $2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$2263,0),0) ,0),0))}



"Mayur Godbole" wrote:

Thank You Guys Im still trying to figure it out and keep getting an error. I
have tried using the solutions you have provided but keep getting an error.
Thank you anyways.

"Mayur Godbole" wrote:

I am using excel for preparing a schedule of Doors. For sorting the data i
have used the following formula
SUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF($K$1:$K $2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$2263,0),0) ,0),0))
which returns a #Value Error.
If i evaluate this formula through Insert<Fuction, i am getting the desired
result, if i evaluate the formula through formula auditing i get the error,
is this a bug in the programme.
The problem is in the if statement, Excel is not evaluating the condition
D2:D2263=1 as a whole but is only evaluating d2:d2263

Any help would be greatly appreciated.

Thank you.

Thanks JonR that helped.....

"JonR" wrote:

It looks like you're trying a conditional sum to evaluate an array. Did you
try holding down the Ctrl-Shift keys when you press <Enter? Your formula
will be enclosed in brackets:

{=
SUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF($K$1:$K $2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$2263,0),0) ,0),0))}



"Mayur Godbole" wrote:

Thank You Guys Im still trying to figure it out and keep getting an error. I
have tried using the solutions you have provided but keep getting an error.
Thank you anyways.

"Mayur Godbole" wrote:

I am using excel for preparing a schedule of Doors. For sorting the data i
have used the following formula
SUM(IF(D2:D2263=1,IF($H$1:$H$2263=E2269,IF($K$1:$K $2263=G2269,IF($E$1:$E$2263="A",$C$2:$C$2263,0),0) ,0),0))
which returns a #Value Error.
If i evaluate this formula through Insert<Fuction, i am getting the desired
result, if i evaluate the formula through formula auditing i get the error,
is this a bug in the programme.
The problem is in the if statement, Excel is not evaluating the condition
D2:D2263=1 as a whole but is only evaluating d2:d2263

Any help would be greatly appreciated.

Thank you.