Search code examples
excelvbacriteria

Excel Criteria NB.SI.ENS

My data were: age If my cell range : B2: B13 The corresponding age between "20" and "29" I wish it count

Criteria "> 20 & < 29"

  =SOMMEPROD(NB.SI.ENS(INDIRECT("'"&{"DECEMBRE"."NOVEMBRE"."OCTOBRE"."SEPTEMBRE"."AOUT"."JUILLET"."JUIN"."MAI"."AVRIL"."MARS"."FEVRIER"."JANVIER"}&"'!" & {"D4:D13"."D4:D18"."D4:D18"."D4:D13"."D4:D26"."D4:D16"."D4:D24"."D4:D18"."D4:D15"."D4:D26"."D4:D24"."D4:D16"});**Criteria Sup of 20 AND Inf 29**))

Thanks you

Solution

  • The criteria just need to be comma-separated (or whatever your local list separator character is) and specified in a string; NB.SI.ENS requires the range to be specified for each criteria, so this should work:

    =SOMMEPROD(NB.SI.ENS(INDIRECT(...);">20";INDIRECT(...);"<29"))