Nested If statements - Sum if

[#1]

Hello the community,

I am trying to write a nested if statement with 2 different fields of data.

One field is called HOURS, the other is called FUNCTION

Basically I want the sum of 'regular' hours of a specific function.

I can't tell where my error is...

=Sum(if(([FUNCTION]='ABC' and HOURS='Regular'), HOURS))

OR

=Sum(if(([FUNCTION]='ABC' and HOURS='Overtime'), HOURS))

If you have any idea....

Thank you

[sunny_talwar · Accepted solution]

Why don't you use set analysis here?

=Sum({<FUNCTION = {'ABC'}, HOURS = {'Regular'}>} HOURS)

=Sum({<FUNCTION = {'ABC'}, HOURS = {'Overtime'}>} HOURS)

[#3]

Agree with sunindia that set analysis is the way to go, but I have to ask:

What you are expecting the sum(HOURS) to evaluate to if this field contains the string values of 'Regular' and 'Overtime'?

[#4]

I thought I tried that earlier, I guess I forgot something...

It works now, Thank you sunindia

[sunny_talwar]

No problem, we are here to help always

Best,

Sunny