I'm just not sure whether this is possible or not.

Can I create a function that goes as below:

=SUMIF(IF(......),Z,A:A)

It seems when I insert an IF statement inside a SUMIF statement, Excel
returns #VALUE! If this is not allowed, how do I get around this problem?