nesting "IF" statements problem

[jvsa]

Dear users,

I am new to this forum and hope to get some help on the following and possible future problems.

I have a problem nesting four "IF" statements into one function.
The several other threads on this problem helped me to no avail.

Here are my four statements:

=IF(AND($H$5="any";$H$7<>"any");SUMPRODUCT((INDIRECT($I$3&"!I30:I40"))*(INDIRECT($I$3&"!D30:D40")=$I$7)))

=IF(AND($H$5<>"any";$H$7="any");SUMPRODUCT((INDIRECT($I$3&"!I30:I40"))*(INDIRECT($I$3&"!C30:C40")=$H$5)))

=IF(AND($H$5<>"any";$H$7<>"any");SUMPRODUCT((INDIRECT($I$3&"!I30:I40"))*(INDIRECT($I$3&"!C30:C40")=$H$5)*(INDIRECT($I$3&"!D30:D40")=$I$7)))

=IF(AND($H$5="any";$H$7="any");SUM(INDIRECT($I$3&"!I30:I40")))

The goal is to retrieve data from another sheet (hence the "INDIRECT" statements), depending on two variables.

The individual statements work perfectly, but combining them in any way gives me errors. I'm not sure if the problem is too many statements in my function.
I'm using Excel 2008 for Mac.

Thank you for your thoughts.

Johan

 

[ClimoC]

You'll kick yourself mate...

you've got semicolons in there. Swap them for commas

("," instead of ";")

I just tried your function with those swapped around, and it works fine

So to revise, it should read:

=IF(AND($H$5="any",$H$7<>"any"),SUMPRODUCT((INDIRECT($I$3&"!I30:I40"))*(INDIRECT($I$3&"!D30:D40")=$I$7)))

Ciao

 

[ClimoC]

Wait - sorry I completely misread your question I just noticed the code. Don't use macs - do they have semicolons instead of commas in their excel? Weird.

Anyway let me have a proper look at it and I'll get back to you in a minute.

 

[jvsa]

Climo,

thanks, I'll try that right now. (and kick myself afterwards)

 

[jvsa]

Indeed,
replacing the semicolons by commas gives me new errors,
I guess it's a mac thing, although we use our excel files cross-platform without problems.
Once again thank you for your efforts.

 

[gecs]

try:

=IF($H$5="any";IF($H$7="any";SUM(INDIRECT($I$3&"!I30:I40");SUMPRODUCT((INDIRECT($I$3&"!I30:I40"))*(INDIRECT($I$3&"!D30:D40")=$I$7)));IF($H$7="any";SUMPRODUCT((INDIRECT($I$3&"!I30:I40"))*(INDIRECT($I$3&"!C30:C40")=$H$5));SUMPRODUCT((INDIRECT($I$3&"!I30:I40"))*(INDIRECT($I$3&"!C30:C40")=$H$5)*(INDIRECT($I$3&"!D30:D40")=$I$7))))

I don't know if on Mac's semicolons are used instead of commas by default, but on Windows PC's if you change the decimal separator to ",", the formula separator is automatically changed to ";".

 

[jvsa]

Gecs,

your function doesn't give me errors, (I had to add a parenthesis at the end), but does not give me the desired result either.
The function is meant to add up hours for several activities spent on different projects, it's on a timesheet basically.
The outcome of the function depends on two variables, both coming from named lists in a different workbook. One variable is "project", the other being "activity". Both of those lists contain a value "any", to sum up all hours spent in all projects on a certain activity, or all hours spent on any activity in a certain project, or to be able to give a sum of all hours spent on any activity for all projects

Only this last number is produced by your function, using other variables always produces "FALSE".
I'll try and change the function you gave me to make it work, your help is highly appreciated.

Thank you, Johan

 

[gecs]

The missing parenthesis had to be added elsewhere, not at the end, but before the third semicolon - I could not test the function otherwise except transforming the SUMPRODUCT(....) sections into strings, so missing that parenthesis when copied from your example lead to such a mistake, sorry.

Try this one:

=IF($H$5="any";IF($H$7="any";SUM(INDIRECT($I$3&"!I30:I40"));SUMPRODUCT((INDIRECT($I$3&"!I30:I40"))*(INDIRECT($I$3&"!D30:D40")=$I$7)));IF($H$7="any";SUMPRODUCT((INDIRECT($I$3&"!I30:I40"))*(INDIRECT($I$3&"!C30:C40")=$H$5));SUMPRODUCT((INDIRECT($I$3&"!I30:I40"))*(INDIRECT($I$3&"!C30:C40")=$H$5)*(INDIRECT($I$3&"!D30:D40")=$I$7))))

it should work if the conditions used in the IF statements are correct.

 

[jvsa]

Dear Gecs,

works like a charm!

Thanks, owe you one.

Johan