Excel: Sum column B if left(A:A,2) ="ZZ" and right(A:A,2)="CA" and mid(A:A,3,1)=4

Question

I'm trying to sum values in column B if:

• First 2 charactersin column A = "ZZ"
• Last 2 characters column A = "CA"
• 3rd character in column A = 4

Tried using sumproduct but no sucess!

Answer 1

This formula should work for you:

=SUMIF(A:A,"ZZ4*CA",B:B)

Answer 2

Found it:

=SUMPRODUCT(--(LEFT(A1:A10,2)="ZZ"),--(RIGHT(A1:A10,2)="CA"),--(MID(A1:A10,3,1)="4"),B1:B10)

However not sure why "--" is needed before each array