Forum Discussion
nested logical tests to get a single number answer
Philip_Spencer Then below formula should work for you.
=COUNTIFS(L:L,"<>",O:O,"<>",R:R,"<>")
Philip_Spencer If you want to use COUNTIFS() then you have to use 3 times like
=COUNTIFS(L:L,"<>")+COUNTIFS(O:O,"<>")+COUNTIFS(R:R,"<>")Alternatively you can use SUM(), CHOOSE() functions.
=SUM(--(CHOOSE({1,2,3},L:L,O:O,R:R)<>0))For older version of excel you need to array entry above choose formula. Array entry means enter formula by CTRL+SHIFT+ENTER.
And for faster approach you can use below formula if you have access to Microsoft365 beta channel or current preview channel.
=COUNTA(TOCOL(CHOOSECOLS(L:R,1,4,7),3))
Thank you for your reply.
I have not expressed my problem well.
My result should only count the number of 'things' which are not blank in all three of these columns, so not blank is O AND not blank in R AND not blank in L.
The result must count what things satisfy all three criteria.
Philip_Spencer Then below formula should work for you.
=COUNTIFS(L:L,"<>",O:O,"<>",R:R,"<>")- Thank you Harun, you have answered my question and taught me how to use the function effectively.