Select a discontinuous range in a function

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

  • Votes
  • Date
  • Descending date
  1. Contributor
    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
    1. 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