VB Countif Function on 3 Columns

[Lonney]

Hello All!

I have been trying to figure this one out, but am having difficulty.

Issue:
I have 3 columns with possible dates in one, two or 3 of the rows. if any cell in any one of the columns has a date entered in the same row, then it should count 1 and so on. In the example below, Row1 has 3 dates, count =1. Row2 has 2 dates. Count = 1 + 1 (total of 2). Row 3 has 0. Count = 1 + 1 + 0(total of 2). Row4 has 1 date. Count = 1 + 1 + 0 + 1(total of 3). Row5 has 3 dates. Count = 1 + 1 + 0 + 1 + 1(total of 4). The final count should be 4.

I coded this as an attempt: =IF(A2<>"",OR(B2<>"",OR(C2<>""))*1,0)
The above code does not fully work. Combined with I need to convert to VB so that I can loop through an unknown number of rows.

Hope this makes sense and someone can help.

Example:
Column1 Column2 Column3
11/18/2014 11/18/2014 11/18/2014
11/18/2014 11/18/2014

11/18/2014
11/18/2014 11/18/2014 11/18/2014

[p24leclerc]

Here is a formula that works. Remark that this is an ARRAY formula and that you have to end its edition by clicking at CTRL+Shift+Enter at the same time. You'll see that Excel is adding {} at both ends of the formula.

Please Login or Register  to view this content.

I'm sure there is other way to do so but this is what I found so far.
Hope this helps

[Lonney]

Thanks p24leclerc!!! Works perfect. I just need to convert that to a macro which loops.

[alansidman]

OP has requested that this be closed. OP will post in CS.