Complex Formula

[Yokoblue]

Hi everybody,

Here's my problem: I have a sheet with all the information about what a person is doing that everybody fill themself. They have a sheet, in the first colomn they put the thing they do from a selected list, in the 2nd row they type what they do, and in the 3rd row they type how many times they work on it. I am currently counting everything they do and how many times they put in it and doing graph with the info.
I currently use the following formula:
=if(Params!B5="";"";add.if(Sommaire!C:C;[@Tâches];Sommaire!E:E))

This formulas Work. It verify if the parameter exist and then proceed to count everything in the colomn C and use the number of hours in column E (Both in another sheet called Sommaire).


Now I want to basicly do the same thing but only for the last 2 weeks. I have the date in colomn B but I can't make it work. Like you see in the following, I'm trying to use the IF command to make it work.
=if(Params!B5="";"";if(Sommaire!B5:B300>=TODAY()-14;add.if(Sommaire!C:C;[@Tâches];Sommaire!E5:E300))) (Doesn't work)

I tested the TODAY()-15 and it show the right thing so there is no problem there. I can't make it so that he only count the info in the rows that have the correct date...

If it can help I can provide a "Pseudo-Copy" of the file. I'll remove everything that isnt related to the case and post it.

Any help is welcomed.
Thx for the help.

 

[Yokoblue]

Hi everybody,

Here's my problem: I have a sheet with all the information about what a person is doing that everybody fill themself. They have a sheet, in the first colomn they put the thing they do from a selected list, in the 2nd row they type what they do, and in the 3rd row they type how many times they work on it. I am currently counting everything they do and how many times they put in it and doing graph with the info.
I currently use the following formula:
=if(Params!B5="";"";add.if(Sommaire!C:C;[@Tâches];Sommaire!E:E))

This formulas Work. It verify if the parameter exist and then proceed to count everything in the colomn C and use the number of hours in column E (Both in another sheet called Sommaire).


Now I want to basicly do the same thing but only for the last 2 weeks. I have the date in colomn B but I can't make it work. Like you see in the following, I'm trying to use the IF command to make it work.
=if(Params!B5="";"";if(Sommaire!B5:B300>=TODAY()-14;add.if(Sommaire!C:C;[@Tâches];Sommaire!E5:E300))) (Doesn't work)

I tested the TODAY()-15 and it show the right thing so there is no problem there. I can't make it so that he only count the info in the rows that have the correct date...

If it can help I can provide a "Pseudo-Copy" of the file. I'll remove everything that isnt related to the case and post it.

Any help is welcomed.
Thx for the help.

Here's a doc that can be used to try to solve my problem... If it can help you help me
To rephrase my problem :

Right now everything work except one thing...

=IF(Params!B5="";"";IF(Sommaire!B100>=TODAY()-14;ADD.IF(Sommaire!C:C;[@Tâches];Sommaire!E:E);ADD.IF(Sommaire!C:C;[@Tâches];Sommaire!E:E)))

It doesnt count with the row B in mind it just count everything if I put a date higher than 2 weeks ago and doesn't count if not.
In programming, it seems easy but in excel I don't know why I cant figure it out.

Basically B100 here is a date 2 days ago. I need it to be the whole row checked everytime...
Anybody can help ?
http://www.2shared.com/file/hNwMKr6y/Complex_Formula_Rev_1.html