One dynamic array works and the other does not.

[kweaver]

In the sheet, K2 : K1001 contain dates.

M2 : M1001 contain payment types for corresponding invoices (e.g., cash, paypal, etc.).
O2 contains paypal, for example.

J2 : J1001 contain amounts paid.

N2 : N1001 contain =MONTH(M2:M1001)
P1 is a 1

This works:

=SUMIFS($J$2:$J$1001,$M$2:$M$1001,$O2,$N$2#,P$1)

Why doesn't this and why is it not an acceptable statement [error says: there's a problem with this formula]?

=SUMIFS($J$2:$J$1001,$M$2:$M$1001,$O2,MONTH(K2:K1001),P$1)

 

[JEC]

Criteria ranges have to be real sheet ranges instead of a calculated array

Same goes for countifs,minifs and maxifs

 

[kweaver]

Criteria ranges have to be real sheet ranges instead of a calculated array

Same goes for countifs,minifs and maxifs

AH HA, I wonder why I didn't know that?

 

[JEC]

I don't like it though, Microsoft has to fix that in my opinion. This however, is working; (used one criteria for this example)

=LET(r,A2:B9,COUNTIFS(INDEX(r,,1),"<>"))