View original (French)

Multiple ranges for COUNTIF?

Koko -  
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   -
Hello,

I would like to know if it is possible to integrate multiple ranges into a single COUNTIF function rather than adding several separate COUNTIF functions.

For example, I would like the function to indicate how many times I find the value 3 in the ranges A1:A10, B1:B20, and C1:C15.

Thank you very much,

Configuration: Windows XP / Internet Explorer 7.0

5 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    No, you have to sum the countifs().

    Eric
    2
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Let the one who put -1 provide their solution, as it will be of great interest to many people...
      0
    2. Lentzouille2 Posted messages 1000 Status Member 39
       
      It is not me.
      0
    3. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
       
      It was me eriiic, but I messed up, so I reset to zero.
      I thought about using the COUNTIFS formula, but it doesn't match what the person wants.

      Sorry.
      0
    4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Too bad, it would have been interesting...
      Have a great day everyone :-)
      Eric
      0
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Good evening
    actually there is a solution, but only if your tables are not likely to contain the sought values in the overflowing parts of the shortest ranges
    you can then write:
    =COUNTIF(A1:C20,3)
    Crdlmnt
    --
    Let’s ask ourselves if we are not the only ones to understand what is being explained?
    1
  3. Lentzouille2 Posted messages 1000 Status Member 39
     
    In the same cell?
    --
    “Life is like a box of chocolates, you never know what you're gonna get.”
    0
  4. Koko
     
    In fact, I simply want to use the COUNTIF function, but my range spans across several different columns.

    If I take my previous example, can the COUNTIF(range;criteria) function contain a range that spans multiple columns? In terms of function, what I'm trying to do would look something like below... but that would actually work!
    COUNTIF((A1:A10;B1:B20;C1:C15);3)
    0
    1. Lentzouille2 Posted messages 1000 Status Member 39
       
      Yes, yes, I understand your problem roughly...

      For example, from A1:A10, you have 5 threes,
      from B1:B20, you have 7 threes,
      from C1:C15, you have 3 threes,

      Do you want to display 5/7/3 or 15?
      0
  6. Koko
     
    I would have liked to display 15 without having to sum several COUNTIFs.

    Since this seems impossible, I will still adopt this technique even though my formulas become a bit cumbersome when multiple columns are used.

    Thank you very much for your help,
    0