If ..... Then ...

[#1]

hi all, could you assist?:

I have 3 columns and I want to calculate the following:

the first column and the second cannot be yes at the same time. If or the first or the second on yes.

now I want to know:

if either "casco boven eigen risico" and/or "casco onder eigen risico" is ja (YES), how many times

the third column "wa aansprakelijk klant" was Ja (YES)

Casco boven eigen risico (niet verhaalbaar)	Casco onder eigen risico (niet verhaalbaar)	WA aansprakelijk klant
Nee	Ja	Nee
Nee	Ja	Nee
Nee	Ja	Nee
Nee	Ja	Nee
Nee	Ja	Ja
Nee	Nee	Ja
Nee	Ja	Ja

so first line is 0, because third column is Nee.

6th line is 0 because 1ste and 2nd are Nee.
However, the 7th (last) line is casco onder eigen risico JA and WA aansprakelijk klant is Ja. That's 1 which I should count/know.

could you help? 

[swuehl]

= -1* sum(  ( ([Casco boven eigen risico]='Ja' ) or ([Casco onder eigen risico])='Ja') ) and ([WA aansprakelijk klant]='Ja' ) )

[#3]

hi swuehl,

formula doesn't work...it says: garbage after expression: ")"

= -1* sum(  ( ([Casco boven eigen risico (niet verhaalbaar)])  ='Ja' ) or ([Casco onder eigen risico (niet verhaalbaar)])='Ja') ) and ([WA aansprakelijk klant]='Ja'))

could you assist?

[swuehl · Accepted solution]

You have used bracketing different from what I suggested:

=-1* sum( ([Casco boven eigen risico (niet verhaalbaar)] = 'Ja'  or [Casco onder eigen risico (niet verhaalbaar)] ='Ja'  ) and [WA aansprakelijk klant]='Ja')

You can also use the condition in the script, please find attached a sample file.