Multiple criteria for sumproduct
 

I am trying to keep a running sum of customer order amounts for a given year
to compare TY vs. LY using the following formula:

=SUMPRODUCT(--('Wholesale Orders'!G2:G2000<=DATE(2008,12,31)),--('Wholesale
Orders'!G2:G2000=DATE(2008,1,1)),--('Wholesale
Orders'!C2:C2000=A2))--('Wholesale Orders'!F2:F2000)

With the G column as the date range, C column as the customer name, and F
column as the $amount.

i do not get an error, but the formula result is an erroneous value. Do the
arrays in sumproduct need to fall in a specific sequence?

Multiple criteria for sumproduct
 

Try...

=SUMPRODUCT(--('Wholesale Orders'!C2:C2000=A2),--('Wholesale
Orders'!G2:G2000=DATE(2008,1,1)),--('Wholesale
Orders'!G2:G2000<=DATE(2008,12,31)),'Wholesale Orders'!F2:F2000)

Hope this helps!

In article ,
ocuhcs wrote:

I am trying to keep a running sum of customer order amounts for a given year
to compare TY vs. LY using the following formula:

=SUMPRODUCT(--('Wholesale Orders'!G2:G2000<=DATE(2008,12,31)),--('Wholesale
Orders'!G2:G2000=DATE(2008,1,1)),--('Wholesale
Orders'!C2:C2000=A2))--('Wholesale Orders'!F2:F2000)

With the G column as the date range, C column as the customer name, and F
column as the $amount.

i do not get an error, but the formula result is an erroneous value. Do the
arrays in sumproduct need to fall in a specific sequence?

Multiple criteria for sumproduct
 

Do the arrays in sumproduct need to fall in a specific sequence?

No

You can eliminate the test for the date range and just test for the year
number:

=SUMPRODUCT(--(YEAR('Wholesale Orders'!G2:G2000)=2008),--('Wholesale
Orders'!C2:C2000=A2),'Wholesale Orders'!F2:F2000)


--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
I am trying to keep a running sum of customer order amounts for a given
year
to compare TY vs. LY using the following formula:

=SUMPRODUCT(--('Wholesale
Orders'!G2:G2000<=DATE(2008,12,31)),--('Wholesale
Orders'!G2:G2000=DATE(2008,1,1)),--('Wholesale
Orders'!C2:C2000=A2))--('Wholesale Orders'!F2:F2000)

With the G column as the date range, C column as the customer name, and F
column as the $amount.

i do not get an error, but the formula result is an erroneous value. Do
the
arrays in sumproduct need to fall in a specific sequence?

Multiple criteria for sumproduct
 

I say your post and wondering if you have advice for a formula I'm tryinig to
write:
C6=4/25/08
C44=Project1213

=sumproduct(--(sheet1!K$2:50001<=C$6),--(Sheet1!E$2:E$50001=C44),Sheet1!T$2:T:50001)

I want to sum datat in column T that has a specific ID in column E that has
a date less than 4/25/08. The reason I have I'm using a specific cell ref
for the date is because I want it to change to the current date. So tommorow
date will be 4/26/08. I keep getting a #Ref! error when I'm writing this,
any ideas?

"Domenic" wrote:

Try...

=SUMPRODUCT(--('Wholesale Orders'!C2:C2000=A2),--('Wholesale
Orders'!G2:G2000=DATE(2008,1,1)),--('Wholesale
Orders'!G2:G2000<=DATE(2008,12,31)),'Wholesale Orders'!F2:F2000)

Hope this helps!

In article ,
ocuhcs wrote:

I am trying to keep a running sum of customer order amounts for a given year
to compare TY vs. LY using the following formula:

=SUMPRODUCT(--('Wholesale Orders'!G2:G2000<=DATE(2008,12,31)),--('Wholesale
Orders'!G2:G2000=DATE(2008,1,1)),--('Wholesale
Orders'!C2:C2000=A2))--('Wholesale Orders'!F2:F2000)

With the G column as the date range, C column as the customer name, and F
column as the $amount.

i do not get an error, but the formula result is an erroneous value. Do the
arrays in sumproduct need to fall in a specific sequence?

Multiple criteria for sumproduct
 

=sumproduct(--(sheet1!K$2:50001<=C$6),--(Sheet1!E$2:E$50001=C44),Sheet1!T$2:T:50001)

As posted, the formula has some ranges not properly defined. It's missing
the column letter in the first array and there's an extra colon in the last
array. However, those won't cause a #REF! error, Excel just won't accept
that as a formula and will display the general "The formula you typed
contains an error" message.

=SUMPRODUCT(--(Sheet1!K$2:K$50001<=C$6),--(Sheet1!E$2:E$50001=C44),Sheet1!T$2:T$50001)


--
Biff
Microsoft Excel MVP


"John" wrote in message
...
I say your post and wondering if you have advice for a formula I'm tryinig
to
write:
C6=4/25/08
C44=Project1213

=sumproduct(--(sheet1!K$2:50001<=C$6),--(Sheet1!E$2:E$50001=C44),Sheet1!T$2:T:50001)

I want to sum datat in column T that has a specific ID in column E that
has
a date less than 4/25/08. The reason I have I'm using a specific cell ref
for the date is because I want it to change to the current date. So
tommorow
date will be 4/26/08. I keep getting a #Ref! error when I'm writing this,
any ideas?

"Domenic" wrote:

Try...

=SUMPRODUCT(--('Wholesale Orders'!C2:C2000=A2),--('Wholesale
Orders'!G2:G2000=DATE(2008,1,1)),--('Wholesale
Orders'!G2:G2000<=DATE(2008,12,31)),'Wholesale Orders'!F2:F2000)

Hope this helps!

In article ,
ocuhcs wrote:

I am trying to keep a running sum of customer order amounts for a given
year
to compare TY vs. LY using the following formula:

=SUMPRODUCT(--('Wholesale
Orders'!G2:G2000<=DATE(2008,12,31)),--('Wholesale
Orders'!G2:G2000=DATE(2008,1,1)),--('Wholesale
Orders'!C2:C2000=A2))--('Wholesale Orders'!F2:F2000)

With the G column as the date range, C column as the customer name, and
F
column as the $amount.

i do not get an error, but the formula result is an erroneous value. Do
the
arrays in sumproduct need to fall in a specific sequence?