Forum Discussion
nested logical tests to get a single number answer
I am looking to solve the following problem is creating a operative formula:
I wish to count how many 'things' there are in my spreadsheet which are not blank in column O and not blank in column R and not blank in column L across several hundred rows of data.
I have tried with countifs, but seem to have my logic wrong.
Your assistance would be welcomed.
Philip_Spencer Then below formula should work for you.
=COUNTIFS(L:L,"<>",O:O,"<>",R:R,"<>")
4 Replies
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,"<>")
