Counting based on two columns in Excel-CodePudding

I have the following table in Excel:

Name      Col_A        Col_B
Michael   Some_value  
Alex      Some_value  Some_value
Jennifer

I want to count in a single cell (without adding any columns to assist me) how many names I have that have at a value at least in one of the columns A or B. So in this case the result will be 2. I tried to do it with COUNTIFS and COUNT (IF) but it seems to cover only one column at a time.

CodePudding user response：

Using MMULT()

• Formula used in cell F4

=SUM(N(MMULT(--(D4:E6<>""),{1;1})>0))

So, we can use either -- or N() which means

The double unary (also called a double negative) is an operation used to coerce TRUE FALSE values to ones and zeros in more advanced formulas, especially formulas that work with arrays.

While N() function converts non-number values to a number, dates to serial numberss, TRUE to 1 and anything else to 0

Note: Source for -- taken from exceljet.net