Sumproduct for YTD with text filter
 

I am trying to sum a customers sales for a year to date value so I can
compare it with the year to date sales for the previous years. The formula is:

=SUMPRODUCT(--('Wholesale Orders'!G2:G2008<=TODAY()),--('Wholesale
Orders'!G2:G2008DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2000)=A5,--('Wholesale Orders'!F2:F2008))

It works perfectly until it calculates array #3, cell #A5 of the current
worksheet is a text value (customer name).

Sumproduct for YTD with text filter
 

Your formula is incorrect. You need a right parenthesis after A5. Assuming
F2:F2008 contain numbers, you do not need "--".

=SUMPRODUCT(--('Wholesale Orders'!G2:G2008<=TODAY()),--('Wholesale
Orders'!G2:G2008DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2000=A5),'Wholesale Orders'!F2:F2008)


Tyro

"ocuhcs" wrote in message
...
I am trying to sum a customers sales for a year to date value so I can
compare it with the year to date sales for the previous years. The formula
is:

=SUMPRODUCT(--('Wholesale Orders'!G2:G2008<=TODAY()),--('Wholesale
Orders'!G2:G2008DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2000)=A5,--('Wholesale Orders'!F2:F2008))

It works perfectly until it calculates array #3, cell #A5 of the current
worksheet is a text value (customer name).

Sumproduct for YTD with text filter
 

I think you want to put the comparison value inside the parenthesis, and also
check the number of elements you've got in that array:
=SUMPRODUCT(--('Wholesale Orders'!G2:G2008<=TODAY()),--('Wholesale
Orders'!G2:G2008DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2008=A5),--('Wholesale Orders'!F2:F2008))


"ocuhcs" wrote:

I am trying to sum a customers sales for a year to date value so I can
compare it with the year to date sales for the previous years. The formula is:

=SUMPRODUCT(--('Wholesale Orders'!G2:G2008<=TODAY()),--('Wholesale
Orders'!G2:G2008DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2000)=A5,--('Wholesale Orders'!F2:F2008))

It works perfectly until it calculates array #3, cell #A5 of the current
worksheet is a text value (customer name).

Sumproduct for YTD with text filter
 

That's it - Thanks!

"bpeltzer" wrote:

I think you want to put the comparison value inside the parenthesis, and also
check the number of elements you've got in that array:
=SUMPRODUCT(--('Wholesale Orders'!G2:G2008<=TODAY()),--('Wholesale
Orders'!G2:G2008DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2008=A5),--('Wholesale Orders'!F2:F2008))


"ocuhcs" wrote:

I am trying to sum a customers sales for a year to date value so I can
compare it with the year to date sales for the previous years. The formula is:

=SUMPRODUCT(--('Wholesale Orders'!G2:G2008<=TODAY()),--('Wholesale
Orders'!G2:G2008DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2000)=A5,--('Wholesale Orders'!F2:F2008))

It works perfectly until it calculates array #3, cell #A5 of the current
worksheet is a text value (customer name).

Sumproduct for YTD with text filter
 

You still do not need the "--" in: --('Wholesale Orders'!F2:F2008) nor do
you need parentheses. 'Wholesale Orders'!F2:F2008 will do
In the first 3 comparisons, the "--" coercing Excel to make TRUE a 1 and
FALSE a 0. With your numbers in F2:F2008, the "--" are
changing the signs of the numbers twice. In other words, 23 becomes -23 (one
minus) and the - 23 becomes 23 (two minuses). In other words,
the "--" is accomplishing nothing.


Tyro

"ocuhcs" wrote in message
...
That's it - Thanks!

"bpeltzer" wrote:

I think you want to put the comparison value inside the parenthesis, and
also
check the number of elements you've got in that array:
=SUMPRODUCT(--('Wholesale Orders'!G2:G2008<=TODAY()),--('Wholesale
Orders'!G2:G2008DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2008=A5),--('Wholesale Orders'!F2:F2008))


"ocuhcs" wrote:

I am trying to sum a customers sales for a year to date value so I can
compare it with the year to date sales for the previous years. The
formula is:

=SUMPRODUCT(--('Wholesale Orders'!G2:G2008<=TODAY()),--('Wholesale
Orders'!G2:G2008DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2000)=A5,--('Wholesale Orders'!F2:F2008))

It works perfectly until it calculates array #3, cell #A5 of the
current
worksheet is a text value (customer name).