Countif filled cells in multiple columns

**vascobmcastro** · 09-07-2010, 01:29 PM

Hi,

I have to count how many rows are filled with data in 3 columns. All the columns can be filled, or just one.. I was thinking on something like countif(OR(column 1, column 2, column 3, "*").

Unfortunately it doesn't appear to be possible to add such thing. Any way around it? SUMPRODUCT?

Vasco

**NBVC** · 09-07-2010, 01:34 PM

Try:

=COUNTIFS(A1:A5,"<>",B1:B5,"<>",C1:C5,"<>")

adjusting ranges to suit..

**vascobmcastro** · 09-07-2010, 01:39 PM

Unfortunately this will only count rows where all the 3 columns have been filled. Maybe my explanation wasn't so good before. What I would like to have is an expression that will count rows that contain at least one cell that is filled in those 3 columns.

**daddylonglegs** · 09-07-2010, 01:46 PM

Try

=SUMPRODUCT((LEN(A1:A100&B1:B100&C1:C100)>0)+0)

**vascobmcastro** · 09-07-2010, 02:00 PM

Exactly!!!!!

Thanks!

2 more things:
- Why the +0 inside sumproduct?
- How do I adapt this formula so only rows that on the 4th column have the country "Austria" would be counted?

vasco

**NBVC** · 09-07-2010, 02:10 PM

I guess I did misinterpret... I thought you said all 3 columns must be filled..

Try:

=SUMPRODUCT((LEN(A1:A100&B1:B100&C1:C100)>0)+0,(D1:D100="Austria")+0)

the +0 coerces the array of resulting TRUEs and FALSEs to 1's and 0's respectively, so Sumproduct can do its math.