Excel: Count rows if more than one column is filled

user_19122683 0
Viewed 23

I have a problem with counting in excel. I need it to count the rows that have value in 2 or more columns.

My sheet is the following

Name Field A Field B Field C Field D
AAA Field A Field C
AAB Field A Field D
AAC Field B
AAD Field B Field D

I need to count rows that have 2 or more values ​​filled within columns Field A, Field B, Field C, Field D

In the example the result should be 3

I have tried with COUNTIF and COUNTIFS but I have not succeeded

How can I do this in Excel 2010?

Thanks

excel
1 Answers

Use MMULT

=SUMPRODUCT(--(MMULT(--(B2:E5<>""),TRANSPOSE(COLUMN(B2:E5)^0))>=2))

Older versions of Excel may need to use Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

or since it is only 4 columns we can do a boolean on each column inside a SUMPRODUCT:

=SUMPRODUCT(--((E2:E5<>"")+(D2:D5<>"")+(C2:C5<>"")+(B2:B5<>"")>=2))

enter image description here

user_48515900
Related