# Complex Formula

#### Yokoblue

##### New Member
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.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Yokoblue

##### New Member
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

Replies
0
Views
47
Replies
1
Views
207
Replies
0
Views
89
Replies
3
Views
173
Replies
1
Views
271

### Forum statistics

Threads
1,126,928
Messages
5,621,639
Members
415,849
Latest member
PhoenixRising2015

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back