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

Answer 1 / 5
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
 
Hello,

No, you have to sum the countifs().

Eric
2
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
Lentzouille2 Posted messages 1000 Status Member 39
 
It is not me.
0
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
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
Answer 2 / 5
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
Answer 3 / 5
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
Answer 4 / 5
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
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
Answer 5 / 5
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