Replacing if/if with if/and returns false (Google Sheets / Excel)-CodePudding

I need cells on Sheet_2 to return a specific value from Sheet_1 (column K) on the condition that the values in other particular cells on Sheet_2 match with cell values on Sheet_1. I already have a pre-made formula that works, but I wanted to change it (I'm still learning Excel and trying stuff out) by replacing IF(IF..)) with IF(AND(..)).

However, the second formula returns 0, and I don't understand why. I think there is a problem with the SHEET_1 references, but I don't know what this problem is.

I've been at it for like 2 hours, so any help would be very appreciated

Works:

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF(
    'SHEET_1'!$L$2:$L$300=I$2;
IF('SHEET_1'!$A$2:$A$300=$Z3;
IF('SHEET_1'!$G$2:$G$300=$A3;
IF('SHEET_1'!$H$2:$H$300=$C3;'SHEET_1'!$K$2:$K$300); 0))))); 1; 1)

Doesn't work:

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF(AND(
'SHEET_1'!$L$2:$L300=J$2; 
'SHEET_1'!$A$2:$A300=$Z3; 
'SHEET_1'!$G$2:$G300=$A3;
'SHEET_1'!$H$2:$H300=$C3); 'SHEET_1'!$K$2:$K300;0); 0))))); 1; 1)

CodePudding user response：

try:

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IF(
('SHEET_1'!$L$2:$L300=J$2)* 
('SHEET_1'!$A$2:$A300=$Z3)*
('SHEET_1'!$G$2:$G300=$A3)*
('SHEET_1'!$H$2:$H300=$C3); 'SHEET_1'!$K$2:$K300;0); 0))))); 1; 1)

CodePudding user response：

within Sheets you can try:

=SUM(IFERROR(FILTER(SHEET_1!K:K;SHEET_1!A:A=Z3;SHEET_1!G:G=A3;SHEET_1!H:H=C3;SHEET_1!L:L=I2)))

=ARRAYFORMULA(SUM(IF((SHEET_1!$L$2:$L300=I$2)*(SHEET_1!$A$2:$A300=$Z3)*(SHEET_1!$G$2:$G300=$A3)*(SHEET_1!$H$2:$H300=$C3); SHEET_1!$K$2:$K300;0);0))