SUMIF with broken range

PS.... I wrote:
> If we can rely on the "X" strings in column A, the following is the
> correct way for you to sum both positive and negative amounts.
> =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z194>0),B4:Z194)
> =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z194<0),B4:Z194)
[....]
> If we cannot rely on the "X" strings in column A, I would suggest that you
> insert a "helper" column with "X" strings. The helper column can be
> hidden.

It just occurred to me that the "X" strings probably represent people's
names, which you prudently edited out of the example file.

But a helper column with "X" strings is not needed. The following should
produce the same results:

=SUMPRODUCT((A4:A194<>"")*(B3:Z3="xc")*(B4:Z194>0) ,B4:Z194)
=SUMPRODUCT((A4:A194<>"")*(B3:Z3="xc")*(B4:Z194<0) ,B4:Z194)

Originally Posted by joeu2004[_2_]

PS.... I wrote:
> If we can rely on the "X" strings in column A, the following is the
> correct way for you to sum both positive and negative amounts.
> =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z194>0),B4:Z194)
> =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z194<0),B4:Z194)
[....]
> If we cannot rely on the "X" strings in column A, I would suggest that you
> insert a "helper" column with "X" strings. The helper column can be
> hidden.

It just occurred to me that the "X" strings probably represent people's
names, which you prudently edited out of the example file.

But a helper column with "X" strings is not needed. The following should
produce the same results:

=SUMPRODUCT((A4:A194<>"")*(B3:Z3="xc")*(B4:Z194>0) ,B4:Z194)
=SUMPRODUCT((A4:A194<>"")*(B3:Z3="xc")*(B4:Z194<0) ,B4:Z194)

I finally got what I wanted by doing this:
=SUMPRODUCT((A4:A194="X10")*(B3:Z3="xc")*(B4:Z194> 0),B4:Z194)
Having a formula for each name: X1, X2 &c.

But the thing is I have quarterly calendars which are set up exactly the same, whatever cell contains a name or label on one, does so in every other one, but it only works in the first sheet. This shouldn't be, should it?