Countifs array results not as expected, can anyone see why?

You don't need SUMPRODUCT, just use the COUNTIFS function.
Your last criteria in the COUNTIFS argument makes no sense to me. Explain your criteria for counting.

I know you're demonstrating something you see that's not working, but an idea of what you're actually trying to do with this formula in the main workbook would be a great help.

here is how i would solve it:

=SUMPRODUCT(($A$1:$A$4="A")*(1/COUNTIFS($A$1:$A$4,$A$1:$A$4,$B$1:$B$4,$B$1:$B$4)))

{=SUM(IFERROR(1/COUNTIFS(A$1:A$4,A$1:A$4,B$1:B$4,B$1:B$4),)*(A$1:A$4="A")})

the condition needs to be outside of COUNTIF(S). apparently, using the "A" condition within the COUNTIF(S) while it is in an array-type formula, elicits a "union" effect, which is visible only if the values in column B are repeating, else, it is business as usual.

Originally Posted by icestationzbra

the condition needs to be outside of COUNTIF(S). apparently, using the "A" condition within the COUNTIF(S) while it is in an array-type formula, elicits a "union" effect, which is visible only if the values in column B are repeating, else, it is business as usual.

Thanks for taking the time to look at this and providing a good theory on the cause, I was getting the feeling that I was missing something obvious, but it would appear to be a bit of a glitch with the function

After looking at your soulutions I tried something else that also works,

{=SUM(IFERROR(1/COUNTIFS(A$1:A$4,A$1:A$4,A$1:A$4,"A",B$1:B$4,B$1:B$4),))}

The thought behind these methods was looking for alternatives to frequency (I can never seem to get the arrays right), but calculation is a bit clunky, think I just need to spend a bit more time trying to get to grips with the tried and tested methods.

i would go only so far to assume that the array-type envelope induces that behaviour, that which is not a glitch in the stand-alone function.