Trouble with COUNTIFs

Hi there,

I'm trying to use the COUNTIFS function.

The following example works fine (it provides a value of 1):

whyworks.png

Formula:

Please Login or Register  to view this content.

However this example does not (it provides a value of 0):

why.png

Formula:

Please Login or Register  to view this content.

The cells that this formula is counting from (columns G to R) are all based on IF functions where na is the false value, but seeing as the first COUNTIFS formula worked I don't see why this should make a difference?

Any thoughts on what might be happening here?

Thanks very much.

I found when you pair up colums in your countifs it causes an issue. it will count the first column of each of the three data ranges together and if it gets a match it will count it. that is why the first one worked. the second one in your example has the data in the first column for range 1 and second column for the red range. it then has data in the first column for the third range. this gave a false result.

this might work for you

Formula:

Please Login or Register  to view this content.

if a range has that value it gives it a value of 1 so if all three ranges has that value then the value is 3 then divided by 3 and rounded down. if there is only 2 of 3 that match then the answer will be less than one and it will round to 0.