Count of occurrences in filtered cells
Solved
mlg35
-
mlg35 -
mlg35 -
Hello everyone,
I can't seem to solve my problem, which I think is relatively simple:
A1: A, B, C
A2: A, B, D, E
A3: B, C, D, E
A4: A, E
A, B, C, D, and E represent word expressions (for example, "I love Excel")
B1: Horse
B2: Dog
B3: Cat
B4: Horse
I filter my sheet on column B with Horse.
Ultimately, I want to calculate how many times A, B, C, D, and E are displayed when I apply a filter (I can find the values without the filter using the formula "=COUNTIF(A1:A4, "A")")
In the example, I should find
A => 2
B => 1
C => 1
D => 0
E => 1
Thank you!
I can't seem to solve my problem, which I think is relatively simple:
A1: A, B, C
A2: A, B, D, E
A3: B, C, D, E
A4: A, E
A, B, C, D, and E represent word expressions (for example, "I love Excel")
B1: Horse
B2: Dog
B3: Cat
B4: Horse
I filter my sheet on column B with Horse.
Ultimately, I want to calculate how many times A, B, C, D, and E are displayed when I apply a filter (I can find the values without the filter using the formula "=COUNTIF(A1:A4, "A")")
In the example, I should find
A => 2
B => 1
C => 1
D => 0
E => 1
Thank you!
1 answer
-
Hello
if it’s about counting the number of visible rows once filtered, you can
create a column out of the field with 1 for each row that contains text
enter at the top of the column this formula:
=SUBTOTAL(9;field;1)
however, without filtering you can find how many cells contain a word included in a text
for example from A2 to A100 the texts in B2 write horse (or another)
in C2 the formula:
=COUNTIF(A2:A100;"*"&B2&"*")
will give you the number of texts that contain the word written in B2
best regards
--
The quality of the response mainly depends on the clarity of the question, thank you!
