looking for non-blank column pairs

[Gizmorama]

I have a sheet with X rows, and columns A-Z

I am looking for the number of instances where adjoining columns are non-blank. Put another way, the number of instances where the number of blank cells between columns is 0.

So if A and B are non-blank, that is 1 instance. Then if L and M are non-blank, that would be instance 2
If A, B, and C are non-blank, that would also be an instance: AB, then BC, 2 instances.

TIA,
WES

[loginjmor]

Hi -

Copy and paste this formula is Cell AB1

=SUMPRODUCT(NOT(ISBLANK(A1:Z1))*NOT(ISBLANK(B1:AA1)))

This will count the number of non-blank adjacent cells in a given row.

Hope that helps.

[loginjmor]

One more thing, if you want to include multiple rows, just change the ranges in the ISBLANK formula for however many rows you want. For example:

=SUMPRODUCT(NOT(ISBLANK(A1:Z2))*NOT(ISBLANK(B1:AA2)))

This would count the non-blank adjacent cells in two rows.

[Gizmorama]

resolves as #N/A for each row.
http://www.excelforum.com/attachment...1&d=1487696994

sample.

[loginjmor]

Hi -

The ranges you entered have to be the same size. B1:M1 and C1:N1. You had B1:N1 which is a bigger array than C1:N1, which makes Excel puke. I have fixed your formula. See attached.

Hope this helps.

[Gizmorama]

Sha-wing! Works

[loginjmor]

Great! If you are happy with the results, please mark this thread as SOLVED per the instructions at the bottom of this post.