Sumproduct for YTD with text filter

  • Thread starter Thread starter ocuhcs
  • Start date Start date
O

ocuhcs

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:G2008>DATE(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).
 
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:G2008>DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2000=A5),'Wholesale Orders'!F2:F2008)


Tyro
 
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:G2008>DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2008=A5),--('Wholesale Orders'!F2:F2008))
 
That's it - Thanks!

bpeltzer said:
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:G2008>DATE(2007,12,31)),--('Wholesale
Orders'!C2:C2008=A5),--('Wholesale Orders'!F2:F2008))
Click to expand...
 
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
 
Back
Top