Can't make countif return the value I want.

I need to check the value of 2 cells based on the counts of 2 ranges. In the attached example I need cells G17 and X17 to both equal 4 if both or one of the counts of ranges (N4:N14) or (AE4:AE14) equal 4. If niether of those ranges equal 4, I need G17 to equal the count of range (N4:N14) and X17 to equal the count of range AE4:AE17. The formula I use in G17 is as follows:

=IF(AND((COUNTIF($N4:$N14,">0")=4),(COUNTIF($AE4:$AE14,">0")=4)),"4",IF(COUNTIF($AE4:$AE14,">0"=4),"4",COUNTIF($N4:$N14,">0")))

X17 is similar but the second half of the formula is reversed. The problem is that when I evalutate the forumla, the third countif returns #N/A and I can't figure out why. In the attached sample, both cells G17 and X17 should return a value of 4, but G17 is returning 9. This happens in both formulas. I am using excel 2019 for this spreadsheet mainly, though I do some work with 2016. I hope I explained that well enough.

The attached workbook does not match what you are describing. Cell references are wrong. Please update the attachment or your description.

I'm not 100% sure, but you seem to be describing this:

G17:=IF(OR((COUNTIF($N4:$N14,">0")=4),(COUNTIF($AE4:$AE14,">0")=4)),4,COUNTIF($N4:$N14,">0"))

X17:=IF(OR((COUNTIF($N4:$N14,">0")=4),(COUNTIF($AE4:$AE14,">0")=4)),4,COUNTIF($AE4:$AE14,">0"))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

You're welcome.

As I said above, please take a moment to mark the thread as solved.