Sumif with 2 conditions

[emmieh]

Good afternoon,

I need to sum the values in row 22 with conditions in two other rows (5 and 6). I have this formula, but it does not work:
=SUM(IF(E$5:CZ$5="1";IF(E$6:CZ$6="LCPE*";E$22:CZ$22;0);0))

Row 5 is 0 or 1 and the condition is 1. Row 6 is a combination of the text LCPE and a number, so LCPE001, LCPE002 etc. The condition in row 6 is that the first four letters of the cell are LCPE. A LEFT function does not work for this. Does anybody know how to solve this?

thank you!

 

[MrKowz]

Why do you have quotes around the 1 in the first condition? Are those values stored as a string or a number?

=SUMPRODUCT(--(E$5:CZ$5=1),--(LEFT(E$6:CZ$6,4)="LCPE"),E$22:C$Z22)

 

[emmieh]

Thanks, that works!