conditional sum returns not expected

G

goss

Hi all.
Using xl xp pro

Here is conditional sum formula

=SUM(IF(FAR_All_Data!$B$2:$B$1622=C$2,IF(FAR_All_Data!$B$2:$B$1622=C$3,IF(FAR_All_Data!$A$2:$A$1622=$A5,FAR_All_Data!$D$2:$D$1622,0),0),0))

Sorry about the wrapping.

Where C$2 = 7001.4500
Where C$3 = 7001.4501
Where $A5 = 54608001
Where Far_All_Data! contains data, all ref's verified
However, all formulas return 0.00. As I copy down the formula Col
(54608001) udates ...002, 003, 004, etc...

Sometimes the expected result is 0.00 other times value should b
greater than 0

Not sure how to read the formula.
Does it say if, or, and?
In other word if this or this and this then sum

It seems it is reading as if and if and if

Any ideas
 
F

Frank Kabel

Hi
if C2 and c3 are different values you'll alsways get zero
as a result. Reson: first you check if column B = C2 and
if this is true you check if column B =C3 (this is always
False)

So you may explain what you're trying to achieve. A
formula is then easily created
 
G

goss

Thanks Frank.

I'm attempting to write formula to add if
account # found in col B on Far_All_Data!
and also unit detected col a same sheet
return sum to comm!

Your response is as I thought.
I guess I could move to a send col to get second account number

But it might be interesting to learn how to do this in one formula.

Here is quasi-algorithm:
If Far_All_Data!$B$2:$B$2000 = c$2 and Far_All_Data!$G$2:$G$2000 = $A
(unit # 54608001) this will update as move down col A.

Or

If Far_All_Data!$B$2:$B$2000 = c$3 and Far_All_Data!$G$2:$G$2000 = $A
(unit # 54608001) this will update as move down col A.

Then su
 
F

Frank Kabel

Hi
Assumption: You want to sum column D. Try
=SUMPRODUCT(--('Far_All_Data'!$B$2:$B$2000=c$2),--('Far_All_Data'!$G$2:
$G$2000=$A5),'Far_All_Data'!$D$2:$D$2000)
 
G

goss

Thanks Frank

Only problem, your formula does not consider condition at C$3
The final formula should consider:

If C$2 and $A5

or C$3 and $A5

then su
 
Top