View original (French)

Select a discontinuous range in a function

Thibodeau19 Posted messages 30 Status Member -  
via55 Posted messages 14391 Registration date   Status Member Last intervention   -
Hello,

How can we select a non-contiguous range of cells as an input range in a function?

For example, I would like to enter the COUNTIF function into row 10 to count the number of cells meeting a certain criterion, but only for the cells in rows 2, 4, 6, and 8 of the corresponding column.

I then want to drag this formula across multiple consecutive columns.

If I had a single column, I could name the group of cells in my input range and use that name in my function, but that won't work in this case, or I would have to create a name for each group of cells for each column.

Thank you for your help!

2 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    if you really only want rows 2, 4, 6, 8 (so the pairs from 2 to 8, this formula for column A

    =SUMPRODUCT((MOD(ROW(A2:A8),2)=0)*(A2:A8="criteria"))

    criteria in quotes only if it's text

    best regards

    --
    To err is human, to persist is diabolical
    0
  2. via55 Posted messages 14391 Registration date   Status Member Last intervention   2 759
     
    Hello Thibodeau

    You can create a custom function

    open the VBA editor (ALT+F11) Insert module and enter the macro: 
    Function NbSiDis(plage, condition) Application.Volatile NbSiDis = 0 For Each p In plage If Application.CountIf(p, condition) Then NbSiDis = NbSiDis + 1 Next p End Function


    Close the editor
    Then in B10 you enter the formula =NbSiDis((B2;B4;B5;B8);your criterion)
    and you can drag the formula in C10 and following

    Best regards
    --
    "Imagination is more important than knowledge." A. Einstein
    0