CODEWITHSUNDEEP
excelformula
Yorgos
Yorgos

Reputation: 30445

How to define a non continuous range in COUNTIF

   A    B   C

1  Β        Β
2  Β        Β
3  Α        Α
4  Α        Α
5  Β        Β
6  Α        Α
7  Α        B
8  Β        Β
9  Β        Β
10 Α        Α
11 Β        A
12 Α        Α
13 Α        Α
14 Β        Β
15 Α        Α
16 Β    ??

In cell B16 I want to count how many "B"s (the value in A16) are in cells C1:C15 and A16. Then using some function I will put an "A" or "B" in cell C16. How can I give such a range in COUNTIF? The same formula must be used for every cell in column B.

Upvotes: 17

Views: 80828

Answers (5)

Kenny
Kenny

Reputation: 11

This also worked! =SUM(COUNTIF(INDIRECT({"J18","J45","J78","J112","J140","J173","J205"}),">1"))

Upvotes: 0

caehm
caehm

Reputation: 11

You can also declare a named range of non-contiguous ranges, but you must use the INDIRECT function in declaring the range. Otherwise, it will fail just like a normal, non-contiguous range.

Declaring the range ("Range1") should have the "applies to" field read:

=INDIRECT({"A1:B2","C4:F17"})

Or use other named ranges:

=INDIRECT({"Table1[Column1]","Table2[Column3]","Table4[Column2]")

Then you can reference it like:

=COUNTIF(Range1,"=Yes")

Upvotes: 1

user1720865
user1720865

Reputation: 191

As an alternative that would work for copying and pasting, you could do:

=COUNTIF($C$1:$C$15,"B") + COUNTIF(A16,"B")

Upvotes: 19

JMax
JMax

Reputation: 26591

For the record, if I understand well, you want to count the value from the cell A16. But if you do such a formula:

=COUNTIF(C1:C15,A16)

You know you will have the value (here B) in cell A16, don't you?

So the value you want to get is:

=COUNTIF(C1:C15,A16) + 1

What did I misunderstand?

Upvotes: 1

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

Here's the formula for cell B16: =SUM(COUNTIF(INDIRECT({"C1:C15","A16"}),"B"))

Upvotes: 24

Related Questions