CODEWITHSUNDEEP
excelexcel-formula
Michi
Michi

Reputation: 5471

FILTER function on column containing numbers, text and empty cells

0 A B C
1 Year From 1974
2 2004 To 2005
3 2016 Result 3
4 1996
5
6 no info
7 1974
8 no info
9
10
11
12

In Cell C3 I want to count the rows in Column A where the year is

a) >= the value in Cell C1 AND
b) <= the value in Cell C2

Therefore, I implemented this formula:

=ROWS(FILTER($A$2:$A$12,($A$2:$A$12>=C1))*($A$2:$A$12<=C2))

However, this formula returns 11 as result which is probably caused by the empty cells and the no info input in Column A.

To avoid that those inputs are considered in the count of the rows I tried to go with this:

=ROWS(FILTER($A$2:$A$12,($A$2:$A$12>=C1))*($A$2:$A$12<=C2)*($A$2:$A$12<>"")*($A$2:$A$12<>"no info"))

However, this formula still returns 11 as result.

How do I have to modify it to get the correct values?

Upvotes: 1

Views: 434

Answers (2)

Harun24hr
Harun24hr

Reputation: 36850

What about COUNTIFS()? Try-

=COUNTIFS(A:A,">="&C1,A:A,"<="&C2)

and with FILTER() try-

=ROWS(FILTER($A$2:$A$12,($A$2:$A$12>=C1)*($A$2:$A$12<=C2)))

enter image description here

Upvotes: 2

Spencer Barnes
Spencer Barnes

Reputation: 2877

You've put double close brackets )) too early in your formula, ending the Filter() function and then putting additional conditions outside of it.

Change the formula to

=ROWS(FILTER($A$2:$A$12,($A$2:$A$12>=C1)*($A$2:$A$12<=C2)*($A$2:$A$12<>"")*($A$2:$A$12<>"no info")))

Upvotes: 1

Related Questions