Help regarding 'IF' function in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using excel for preparing a schedule of Doors. For sorting the data i
have used the following formul
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 (e-mail address removed)"Mayur Godbole" <Mayur (e-mail address removed)> wrote in messageam 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 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
 
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
 
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=what))--Don GuillettSalesAid (e-mail address removed)"Mayur Godbole" <[email protected]> wrote in messageThe 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 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 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
 
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.
 
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))}
 
Thanks JonR that helped.....

JonR said:
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))}
Click to expand...
 
Back
Top