Forum Discussion

Vandewz's avatar
Vandewz
Copper Contributor
Jan 08, 2021

Calculation NB.SI.ENS

Hi,

 

i'm french so sorry if i'm not really clear in my explanation.

 

I'm using the following calculation

=NB.SI.ENS(B7:B388;"=24";C7:C388;"<31/12/2017")

 

which mean every number 24 with a date before 31-DEC-2017 will be counted.

but on my side, i try to have every number 24 of the year 2017 only

 

i tried the following

 

=NB.SI.ENS(B7:B388;"=24";C7:C388;"=2017")

=NB.SI.ENS(B7:B388;"=24";C7:C388;"=XX/XX/2017")

 

but it doesn't work..

can you please help ?

 

I tried this and it works :

 

=NB.SI.ENS(B7:B388;"=24";C7:C388;"<31/12/2017";C7:C388;">01/01/2017")

 

!

 

excel

3 Replies

  • Jihad Al-Jarady's avatar
    Jihad Al-Jarady
    MCT
    Jan 08, 2021

    Vandewz 

     

     

    =NB.SI.ENS(B7:B388;"=24";C7:C388;"=2017")

    The second criteria will not match since it is just matching the whole date such as "31-5-2017" with just "2017" 

     

    =NB.SI.ENS(B7:B388;"=24";C7:C388;"=XX/XX/2017")

    This second criteria will search for  "XX/XX/2017" it will not match anything, it seems you are trying to use wildcard but it does work here.

     

    =NB.SI.ENS(B7:B388;"=24";C7:C388;"<=31/12/2017";C7:C388;">=01/01/2017")

    This is one sure should works, just make sure you added = to include both dates, if 01/01/2017 is the 24 number as per your formula it will not count, and the same for 31/12/2017

     

    * If my post helped you, please click on like.

     

Resources