Countif on steroids

[passion_to_be_free]

Ok, so I have a worksheet where each row represents one person, and
there are four columns per person that could potentially have data in
them. I want to count the number of people that have data in any one
of the columns.

For example, one person may only have data in column B, another may
only have data in column C and I want to count both of them. However,
if someone has data in BOTH column B and column C, I only want to count
that person once.

I've tried something like this:

=SUMPRODUCT((B13:B151>0)+(C13:C151>0)+(D13151>0)+(E13:E151>0))

and it comes close, but if a person has data in two or three columns,
they get counted two or three times, respectively. Is there any
function to help me count these people?

 

[JE McGimpsey]

One way:

Assuming all your data are positive numbers:

=SUMPRODUCT(--((B13:B151+C13:C151+D13151+E13:E151)>0))

If instead you just want to calculate rows which have some data
(numbers, text):

=SUMPRODUCT(--((B13:B151<>"")+(C13:C151<>"")+(D13151<>"")+
(E13:E151<>"")>0))

 

[JE McGimpsey]

And another:

=SUMPRODUCT(--(LEN(A13:A151&B13:B151&C13:C151&D13151)>0))

or, if you want to ignore space characters:

=SUMPRODUCT(--(LEN(TRIM(A13:A151&B13:B151&C13:C151&D13151))>0))

 

[passion_to_be_free]

Thanks!! It works!! Let me see if I understand...what we're telling
Excel to do is take a sum of those four columns. If that sum is >0
(meaning that at least one column has data in it), then count that row.
Is that right?

Thanks so much!

 

[JE McGimpsey]

Thanks!! It works!! Let me see if I understand...what we're telling
Excel to do is take a sum of those four columns. If that sum is >0
(meaning that at least one column has data in it), then count that row.
Is that right?

For the first formula I gave, yes, exactly. Again, it depends on all
values being positive. {-1, 0, 1, 0} would not be counted.