Aug 25 2020 10:02 PM - edited Aug 25 2020 10:35 PM
Hi everyone,
I would like to achieve a COUNTIFS Function on 3 columns. If the criteria is NO, then it counts as 1.
How can I achieve this in Excel? Currently I'm using =COUNTIFS Function tho it's not really working as it is.
Thanks!
Aug 25 2020 10:41 PM
Aug 25 2020 11:16 PM
COUNTIFS handles 2D ranges perfectly well although it is not widely used in this way.
= COUNTIFS(range, "NO")
where 'range' might be
=Table1[[Column1]:[Column3]]
The catch is that the criterion ranges must be actual range references and not just arrays and, where there are multiple criterion ranges, they must all be of identical size.
Aug 25 2020 11:27 PM - edited Aug 25 2020 11:35 PM
Hi @JMB17,
Thanks! However, I'm not sure if it's working tho or I could be doing it wrong. The count should be 2 in the last column since there are 2 "No" listed there.
Attached is the file for your reference.
Thanks!
Aug 26 2020 01:52 AM
SolutionYou may use
=SUM(--(Q2:S2="No"))
as it is if your Excel supports dynamic arrays, otherwise enter as array formula or use SUMPRODUCT instead of SUM
Aug 26 2020 07:11 AM
Aug 26 2020 01:52 AM
SolutionYou may use
=SUM(--(Q2:S2="No"))
as it is if your Excel supports dynamic arrays, otherwise enter as array formula or use SUMPRODUCT instead of SUM