Need a formula calculation dependent on the filter displayed.

[RemyDup31]

Hello friends, 

First of all, i'm sorry for my English, i'm french :)

So, i would like to apply formula dependent on the filter displayed.

I've created an Excel sheet to manage projects with start date, step 1 date, step 2 date, and again ..

But i need to know :

1.  The reactivity between two date (Number of days between two date (Column E and F) 
2.  I need to apply a formula to this result (Number of projets <= at 7 days).

Once i have the number of days for all projects and number of days "<=7", I need to apply a final formula :

1.  Create a percentage of among all reactivity date, how many percent are <=7 days

AND, i need to manage all the formula to obtain the percent between project start date i choose.

My issues :

• I've some trouble to apply the formule only on the column displayed with selected filter.
• I need to exclude empty cells because some of my project start with the first date but they don't have yet the second date (But the line need to bee seen with the filter if needed)

Can you help me ? Do you have answers ?

Thanks a lot !!

[#2 · Accepted solution]

@RemyDup31 

To count only non-filtered rows you need to add helper column with formula like

=AGGREGATE(3,5,[@[Etat du projet]])

which returns zero for filtered column and 1 for visible (here is in column X).

Count projects with less than 7 days in duration and ignore blanks you may use

=COUNTIFS([Réactivité
IN01-RD2],"<=7",[Date Analyse 
(2-FAD)],">1",[Helper],"=1")

French notation of formulas will be within file.

[#3]

Je parle français :)

[#4]

Yes !! Thanks a lot ! I just add SUB TOTAL function to calculate the percentage but it works great ! 

=(NB.SI.ENS([Réactivité
IN01-RD2];"<=7";[Date Analyse 
(2-FAD)];">1";[Helper];"=1"))/(SOUS.TOTAL(103;[Réactivité
IN01-RD2]))

Thank you @Sergei Baklan !!

[#5]

@RemyDup31 , you are welcome