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
CodePudding user response：
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.
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))
