Complex Formula

Yokoblue

New Member
Joined
Jul 12, 2012
Messages
2
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Yokoblue

New Member
Joined
Jul 12, 2012
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,538
Messages
5,625,398
Members
416,100
Latest member
lirongr1996

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

Which adblocker are you using?

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
Top