BusinessObjects Board

Formula not working - Sumif with 2 conditions

building reports webintelligence
#1

I can’t seem to get a sum formula to work when there are 2 separate ‘equals to’ conditions. Here is what I have tried.

=If([Code1]=“100” And [Code2]=“250”) Then Sum([Amount])
This one gives me a #MULTIVALUE

=Sum([Amount] Where([Code1]=“100” and [Code2]=“250”))
This one is returning no values for me.

Just to try something different in my troubleshooting, I found that this one gave me a value that I could validate.
=Sum([Amount] Where([Code1]=“100” and [Code2]<>“250”))

Am I not able to have 2 = conditions? I only want a sum of amounts that meet both conditions. Not sure why the second formula above isn’t working for me.

#2

Try moving your parenthesis

= Sum( [Amount] ) Where( [Code1] =“100” and [Code2] = “250” )

#3

You need to wrap the sum round your whole logic so that it treats the if then else one row at a time then aggregates the answer.

=Sum(If([Code1]=“100” And [Code2]=“250” Then [Amount])

Alternatively, use @dtolley’s approach

1 Like
#4

Hmm, I see what he means. I tried re-writing the formula as follows, which would seem like it would work, but the cell returns as blank as well. Why would the cell return blank? There is definitely data to sum up.

=Sum ([Amount] Where ([Code 1] InList (“575”) And ([Code 2] InList (“R”))))

It’s really the same as =Sum([Amount] Where([Code1]=“100” and [Code2]=“250”))

It should work. Anyone know why it would not?

#5

try to simplify your formula to only one comparison
and check if Code1/2 are Text- or Number-format

#7

@N8AKTIV I think it is because one piece of data is stored as TEXT when it should be a NUMBER.

Where in Webi can I force it to read as a number?

#8

NM, I need to use =IsNumber

Got it. An example would look like this:

=Sum([Amount] Where([Code 1]=500 And IsNumber ([Code 2]=“795”)))

#9

check if it is a number: isnumber(…
convert into number: tonumber(…

#10

If I’d got this far down the rabbit hole, I’d now push back to the universe designer asking for them to clean it up and give me better value objects to work with.

2 Likes