SOLVED

New Contributor

# How to COUNT with condition: Cell to right is (also) NOT BLANK

Hi,

Do you know a formula to count the number of cells not blank in a range (in example table A1:A5), but with a condition that the cell to the right (in example table B1:B5) is also not blank.

Another way to explain is that I want to count the # of rows within a row range (in example table 1:5) where two columns (in example table A:B) are not blank.

The result to the problem based on the example would be 2 (only A1 and A4//row 1 and 4 fulfill the criteria).

EXAMPLE TABLE:

 A B 1 2 1 2 3 1 4 3 2 5 1

I hope you can help. Thank you in advance.

4 Replies
Best Response confirmed by ablixt (New Contributor)
Solution

# Re: How to COUNT with condition: Cell to right is (also) NOT BLANK

@ablixt Try this:

``=SUMPRODUCT(--(A1:A5<>"")*--(B1:B5<>""))``

# Re: How to COUNT with condition: Cell to right is (also) NOT BLANK

A bit confused by request:

... a formula to count the number of cells not blank in a range but with a condition

AND

... to count the # of rows within a row range

are two different things. If you want to simply count any cells that are not BLANK, you can use the COUNTA function

# Re: How to COUNT with condition: Cell to right is (also) NOT BLANK

@Riny_van_Eekelen exactly what I was looking for. Thank you!

# Re: How to COUNT with condition: Cell to right is (also) NOT BLANK

Thank you for taking your time to answer. I meant either/or. But I got the help I needed.