CODEWITHSUNDEEP
excelexcel-formula
aliceinwonderland
aliceinwonderland

Reputation: 29

Get Union Count Over Multiple Columns -- Excel

Would like to what excel functions can count the row numbers when at least one column contains certain values? For example, from the below table, if I want to get the row number with at least one column being NULL, the answer should 3

header A B C
First 'NULL' 'NULL' 'NULL'
Second 'NULL' 'WORD' 'NULL'
Third 'WORD' 'NULL' 'WORD'

Thank you so much for your help! Much appreciated!!

Upvotes: 1

Views: 298

Answers (2)

Harun24hr
Harun24hr

Reputation: 37050

With Excel365 you do it like-

=COUNTA(FILTER(A2:A4,(B2:B4="Null")+(C2:C4="Null")+(D2:D4="Null")))

enter image description here

If it is truly null means blank then use-

=COUNTA(FILTER(A2:A4,(B2:B4="")+(C2:C4="")+(D2:D4="")))

Upvotes: 2

Elkoss
Elkoss

Reputation: 46

Easiest way is to add a new column with following function:

=IF(COUNTIF(A1:C1,"'NULL'")>0,1,0)

and then sum all the values in that column

Upvotes: 0

Related Questions