Solved: IF Statement doesn't work

[MPDK166]

I have the following statement and the outcome should be true, but is false. It seems that the if statement doesn't work with ranges.

=IF(Opleidingen!B7:B30=1;SUM.IF(Opleidingen!G7:G30;3;Opleidingen!E7:E30);0)

What is wrong with this formula?


Also I have got VBA in this sheet and therefore the value -0- will not be displayed anymore. How to solve?

[Bob Phillips]

Explain your business logic, I can't fathom out what you are trying to do from the formula that you have provided.

[Aussiebear]

=IF(Opleidingen!B7:B30=1;SUM.IF(Opleidingen!G7:G30;3;Opleidingen!E7:E30);0)

What is wrong with this formula?[/quote\

Shouldn't there be an opening parentheses "(" between Sum.If, and naturally a closing parent somewhere later on, or is this European thing?

[quote]Also I have got VBA in this sheet and therefore the value -0- will not be displayed anymore. How to solve?

Best solution here is to upload the code to the forum so we can see how it is currently structured. Then we will be in a better position to assist you in your request

[MPDK166]

This formula works:
IF(Opleidingen!B7=1;SUM.IF(Opleidingen!G7:G30;3;Opleidingen!E7:E30);0)

However, this one doesn't work:
IF(Opleidingen!B7:B30=1;SUM.IF(Opleidingen!G7:G30;3;Opleidingen!E7:E30);0)

So, I guess the range in the if statement is not correct!

[Bob Phillips]

The first works because you are testing a single cell for a value. The second is trying to test a range, IF doesn't do that. Again I ask, what is the business logic, tell us that, we can tell you how to do it.

[MPDK166]

See attachment...

In sheet Voorblad Range(B25:F37) I have got the formula.

If I fill in in sheet Opleidingen some data, this must be summarized in sheet Voorblad.

In sheet Opleidingen you have got to fill in two variables. Column B (value: 1 - 6) and column G (value: 1 - 13)

E.g.

Sheet Opleidingen:
B7 = 1; E7 = 5; G7 = 1
B8 = 1; E8 = 7; G7 = 1
B9 = 2; E9 = 5; G8 = 2
B10 = 1; E10 = 5; G10 = 3
B11 = 2; E11 = 6; G11 = 1

The results in sheet Voorblad should be:

B25 = 12
B27 = 5
D25 = 6
D26 = 5

[Bob Phillips]

Try these

=SOMPRODUKT(--(Opleidingen!$B$7:$B$30=1);--(Opleidingen!$G$7:$G$30=ROW($A1));Opleidingen!$E$7:$E$30)
=SOMPRODUKT(--(Opleidingen!$B$7:$B$30=3);--(Opleidingen!$G$7:$G$30=ROW($A1));Opleidingen!$E$7:$E$30)
=SOMPRODUKT(--((Opleidingen!$B$7:$B$30=2)+(Opleidingen!$B$7:$B$30=5)+(Opleidingen!$B$7:$B $30=7));--(Opleidingen!$G$7:$G$30=ROW($A1));Opleidingen!$E$7:$E$30)
=SOMPRODUKT(--(Opleidingen!$B$7:$B$30=6);--(Opleidingen!$G$7:$G$30=ROW($A1));Opleidingen!$E$7:$E$30)
=SOMPRODUKT(--(Opleidingen!$B$7:$B$30=4);--(Opleidingen!$G$7:$G$30=ROW($A1));Opleidingen!$E$7:$E$30)

[MPDK166]

Thanks,

But unfortunately it is not working... Other ideas?

[Aussiebear]

Please attach a sample workbook with the same IF Statement layout so we can better understand what is happening here.

[Bob Phillips]

Thanks,

But unfortunately it is not working... Other ideas?

It worked for me, albeit my formulae were English and I translated for you.

Plug those formulae into your workbook, add some data that will return results, and post the revised workbook.

[MPDK166]

Wonderfull,

You can also upload a workbook... so i can copy paste it, since my excel version translate it automatically.

[Bob Phillips]

Sorry! I am not sure what happens next from that reply.

[MPDK166]

Sorry,

Readed to quickly....

Attached a file with data!

[Bob Phillips]

Here you are.

[MPDK166]

XLD,

Thank you very much!