View original (French)

Even/Odd Function in a Range (COUNTIFS)

Solved
pasbra Posted messages 9 Status Member -  
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   -
Hello,

I use the NB.SI.ENS function with 2 criteria. I want to set a criterion from a range if the value is Even or Odd. I know the EST.PAIR and EST.IMPAIR functions, but they seem to apply to a single value, not to a range.

My first criterion is a text value and my second criterion is an even value.

My formula:

=NB.SI.ENS(F30:F37,"1*"; E30:E37,"EST.PAIR(E30:E37)")

the bold part doesn’t work...

I can use a mathematical criterion like >23, it works but it doesn’t meet my criterion.

Thanks for your advice

Configuration: Windows / Chrome 62.0.3202.94

-- Pascal

4 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello

    there aren’t too many explanations in your file, but if you’re trying to count the courses that start with 1 in E with an even number in F, this should work:

    =SOMMEPROD((GAUCHE(F2:F1000;1)="1")*(MOD(E2:E1000;2)=0))

    note, be sure to place the 1 of the first item in quotes to treat it as text.

    limit the number of rows to the useful height to avoid the heaviness of this formula

    regards

    The quality of the answer depends mainly on the clarity of the question, thank you!
    1
    1. pasbra
       
      Hello,

      Thanks, it works. Using MOD is an excellent idea to detect if a number is even...
      0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      You're welcome
      I'm marking the topic as resolved
      Have a good journey
      Regards
      0
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    according to your text, it is about counting even values if the text in column F starts with 1

    try with:

    =SUMPRODUCT((LEFT(F30:F37;1)*1=1)*(MOD(E30:E37;2)=0))

    the *1 in the first item is to be placed only if the values in F are numeric

    and you can add more, for example:

    =SUMPRODUCT((LEFT(F30:F37;1)*1=1)*(Y30:Y37>23)*(MOD(E30:E37;2)=0))

    and more still

    regards

    --
    The quality of the answer depends mainly on the clarity of the question, thank you!
    0
    1. pasbra Posted messages 9 Status Member
       
      Thank you for your response. By applying directly the formula as I mentioned, it works. However, I cannot use it in my case. Column F represents courses and column E a sequential # used to distinguish whether the course is in the morning or in the afternoon. My goal is to count the number of X courses in the morning; the number of Y courses in the afternoon... and so on, to total the number of hours since the courses don’t have the same duration in AM or PM. Thinking about it, I think I’ll add a AM or PM column based on whether the # is even or odd. After that it will be easy to continue... I think! Thanks again
      0
  3. DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717
     
    Hello to you both

    try:

    [=SUMPRODUCT((F30:F37="1*")*(IS.PAR(E30:E37))*(E30:E37))]
    in array

    Best regards
    0
    1. pasbra Posted messages 9 Status Member
       
      Hello, I tried, but it gives me an error message. I don’t exactly understand what you’re trying to do; it’s harder to find the fault at this time... Is NB the function you want to use or NB.SI? I tried several things, but I can’t get anything to work. In matrix form, what did that mean and do the "" ] "" matter? Thanks
      0
      1. DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717 > pasbra Posted messages 9 Status Member
         
        you write your formula

        =NB(SI((F30:F37="1*")*(EST.PAIR(E30:E37);E30:E37)) and you validate it by shift +control+enter with 3 fingers at the same time.
        Result then appears on both sides of your formula with braces showing that your formula is validated
        Result obtained :
        {=NB(SI((F30:F37="1*")*(EST.PAIR(E30:E37);E30:E37))}
        0
      2. pasbra Posted messages 9 Status Member > DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention  
         
        It doesn’t work... There’s an error in the formula...

        It doesn’t seem possible to attach a file here?
        0
      3. DjiDji59430 Posted messages 4278 Registration date   Status Member Last intervention   717 > pasbra Posted messages 9 Status Member
         
        Hello, Join a test file, (with ALL explanations and the desired results) or better, if possible, the concerned file, it’s more convenient for the contributors. 1) Go to https://www.cjoint.com/ 2) Click on [Browse] to select your file 3) Scroll down the page to click on [Create the Cjoint link] 4) After a few seconds the blue underlined link will appear; select it and click "Copy" 5) Return to the discussion, and in your reply, paste it. Best regards. {=NB(SI((GAUCHE(F30:F37;1)=1)*(EST.PAIR(E30:E37);E30:E37))}
        0
  4. pasbra
     
    Thank you, I hadn’t known about the site!

    http://www.cjoint.com/c/GKrbbmwiDWB
    0