How to make count function in excel count this cell only if another column in that row is not blank

1
votes

I am trying to count a column in Excel but I want it to be counted only if it is not blank and has value in another column.

enter image description here

Here, I want to count the number of cells in Text2 which are not blank and also have value in Text1. In this case the value should be 3

I have tried Count(), Countif() with NOT(ISBLANK()) etc. But nothing seems to work.

Can someone help me out ?

Thanks.

2
excel
show the code you have ... – matzone

2 Answers

4
votes

Please try: 

=COUNTIFS(B2:B11,"<>",C2:C11,"<>")
1
votes

This trick works with Excel and relational databases. Add a new column and set the value to 1 if your complex conditions are met. Set the value to 0 if they are not met. Then sum that new column and you will have your answer.

Sorry but I don't have a copy of Excel handy, but I think that E2 would have the formula

=IF(AND(IF(C2<>"",TRUE,FALSE),IF(B2<>"",TRUE,FALSE)),1,0)

You may have to adjust the formula, but the trick is universal.